Tagged Questions
3
votes
2answers
44 views
sp_executesql adds statements to executed dynamic script?
The Question:
As far as I can tell, sp_executesql adds statements to the beginning of submitted dynamic SQL script. But, a SQL Profiler trace does not capture the extra statements, and neither does ...
3
votes
2answers
76 views
Find all the SP's where a particular column is not updated
I want to find stored procedures where a particular column is not updated.
As an example
SP1:
BEGIN
UPDATE YourTable
SET Foo = @Foo,
Bar = @Bar
WHERE Id = @Id
...
1
vote
2answers
432 views
How to make a flexible service broker receive in a stored sql procedure, how to pass “FROM” parameter into receive
a while ago I asked a question about how to make begin dialog and send more flexible so that it can be embedded in a procedure that takes the to, from, parameters as sysname variables.
Service Broker ...
4
votes
2answers
126 views
Generate script to automate renaming of default constraints
Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like: DF__User__TimeZoneIn__5C4D869D
I would prefer to ...
7
votes
2answers
2k views
Create View in specified database with dynamic sql?
I am writting a dynamic sql to drop and create view in different database.
So I wrote:
set @CreateViewStatement =
'
USE ['+ @DB +'];
CREATE VIEW ...
7
votes
2answers
226 views
Why does this cursor produce results in the incorrect order?
I am writing some dynamic SQL to identify and, perhaps if I'm feeling crazy enough, automatically convert my NONCLUSTERED indexes into CLUSTERED indexes.
The line ORDER BY 1,2,3 DESC; in the SQL ...
5
votes
2answers
190 views
Why you want to avoid Dynamic SQL in stored procedure?
I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's ...
2
votes
1answer
756 views
sp_execute expects parameter '@handle' of type 'int'
I'm trying to check in a stored procedure if a table exists in my destination database. If it does not then I will create the table useing the information_schema tables from the source database. ...
9
votes
1answer
3k views
Stored Procedure to return dynamically created table data
Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
4
votes
1answer
400 views
sp_ExecuteSQL, performance and table variables
Is anyone able to explain some behaviour I'm seeing with SQL Profiler? Taking the following two batches:
exec sp_executesql N'
declare @t table (
x int
)
insert into @t (x) ...
2
votes
2answers
6k views
How to include a datetime parameter within a stored procedure along with string query?
My stored procedure is as follows,
-- Add the parameters for the stored procedure here
@FromDate datetime,
@ToDate datetime
--Select query
DECLARE @query nvarchar(max)
...
3
votes
2answers
179 views
switch TOP clause between percentage and rows by parameter
For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows.
I have two ideas
Calling two different sub sproc based on passed param.
if ...
5
votes
1answer
1k views
Print Parameters in Dynamic SQL
I've used dynamic SQL for many tasks and continuously run into the same problem: Printing values of variables used inside the Dynamic T-SQL statement.
EG:
Declare @SQL nvarchar(max), @Params ...
