November 6, 2012 at 7:48 am
My final dynamic @sql would be:
Select DocID, Code, Ref, Title, DocType, FileName, InitialSignOffDate, LastReviewDate, NextReviewDate, Due = Replace(@Due, 'in', 'Within'), DueValue, SA, Type, Category, Division, ITSDivision, Contact, Phone) From ##temp where DueValue <= 60 Order by NextReviewDate ASC
I am running it by:
exec (@sql)
But it throws me error that @Due is not defined. In this case, how do I revise my code? Thanks. The @sql was built through lots of other codes, I want the new exec be as simple as possible and no need to do too much on @sql.
November 6, 2012 at 7:55 am
You'd have to define the variable inside the scope of the Exec command in order to do it that way. You're better off using sp_executeSQL instead.
EXEC sp_executeSQL 'Select DocID, Code, Ref, Title, DocType, FileName, InitialSignOffDate, LastReviewDate, NextReviewDate, Due = Replace(@Due, 'in', 'Within'), DueValue, SA, Type, Category, Division, ITSDivision, Contact, Phone) From ##temp where DueValue <= 60 Order by NextReviewDate ASC',
'@Due VARCHAR(1000)',@Due;
Define @Due in the second piece there the same way you do in the proc/script that runs this command.
Using sp_executeSQL this way helps prevent SQL injection attacks.
Specifics on sp_executeSQL can be found here: http://msdn.microsoft.com/en-us/library/ms188001.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 6, 2012 at 8:02 am
GSquared (11/6/2012)
You'd have to define the variable inside the scope of the Exec command in order to do it that way. You're better off using sp_executeSQL instead.
EXEC sp_executeSQL 'Select DocID, Code, Ref, Title, DocType, FileName, InitialSignOffDate, LastReviewDate, NextReviewDate, Due = Replace(@Due, 'in', 'Within'), DueValue, SA, Type, Category, Division, ITSDivision, Contact, Phone) From ##temp where DueValue <= 60 Order by NextReviewDate ASC','@Due VARCHAR(1000)',@Due;
Define @Due in the second piece there the same way you do in the proc/script that runs this command.
Using sp_executeSQL this way helps prevent SQL injection attacks.
Specifics on sp_executeSQL can be found here: http://msdn.microsoft.com/en-us/library/ms188001.aspx
Thanks for the quick reply. Much appreciated.
I tried to do this:
EXEC sp_executeSQL @sql, @Due
It throws me new error:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
There is no parameter of @statement
November 6, 2012 at 8:15 am
halifaxdal (11/6/2012)
GSquared (11/6/2012)
You'd have to define the variable inside the scope of the Exec command in order to do it that way. You're better off using sp_executeSQL instead.
EXEC sp_executeSQL 'Select DocID, Code, Ref, Title, DocType, FileName, InitialSignOffDate, LastReviewDate, NextReviewDate, Due = Replace(@Due, 'in', 'Within'), DueValue, SA, Type, Category, Division, ITSDivision, Contact, Phone) From ##temp where DueValue <= 60 Order by NextReviewDate ASC','@Due VARCHAR(1000)',@Due;
Define @Due in the second piece there the same way you do in the proc/script that runs this command.
Using sp_executeSQL this way helps prevent SQL injection attacks.
Specifics on sp_executeSQL can be found here: http://msdn.microsoft.com/en-us/library/ms188001.aspx
Thanks for the quick reply. Much appreciated.
I tried to do this:
EXEC sp_executeSQL @sql, @Due
It throws me new error:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
There is no parameter of @statement
The error message states it about as clear as it could be. Change your datatype of @sql to nvarchar and you should be good to go.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 6, 2012 at 8:22 am
First, is @SQL defined as varchar, or nvarchar? Needs to be unicode for this (nvarchar).
Second, you need to provide sp_executeSQL with (at least) 3 things in order to use parameters in it:
The command
A list of the parameters in it
The values.
Example:
DECLARE @SQL NVARCHAR(MAX) = 'SELECT @val as Val;', @val INT = 1;
EXEC sp_executeSQL @SQL, N'@Val INT', @val;
Example 2:
DECLARE @SQL NVARCHAR(MAX) = 'SELECT @val as Val, @Val2 as Val2;';
EXEC sp_executeSQL @SQL, N'@Val INT, @Val2 INT', 1, 2;
The first value passed to sp_executeSQL is the command to be executed, which has to be defined as an nvarchar string. It can be a variable, or a fixed-value, but not a constructor.
Wrong example:
EXEC sp_executeSQL N'SELECT ' + CAST(1 AS NCHAR(1))
That won't work because it use a string-constructor, instead of a variable or a fixed string.
If you look at the second example, you'll see a list of the parameter values at the end of the call to sp_executeSQL, in the form of "1,2". Right before that, it has a string that defines the parameters, just like at the beginning of a stored procedure. Again, this has to be Unicode (nchar/nvarchar), and it can be either a variable or a fixed string, but not a string-constructor. Same as the SQL command.
The other way to build variable values into dynamic SQL is simpler, but less safe and effective.
DECLARE @Val1 VARCHAR(1000) = '1';
DECLARE @SQL VARCHAR(100);
SET @SQL = 'SELECT ' + @Val1 + ' AS Val;';
EXEC (@SQL);
That example treats @Val1 as an input parameter, concatenates it into the @SQL string, then executes the string.
Why is that a bad idea? Because what if someone provides this as the "value" for @Val1 "hello' or 1=1;DROP TABLE dbo.Users--"? Run that in an Exec() call, and you may very well end up with a table being dropped. This is called "SQL injection", and it is one of the most common tools used by hackers.
If someone does that to parameterized calls to sp_executeSQL, it's treated as an input, and the drop table part is just a string, it doesn't get executed, so your database is safe. That's one of the main differences between Exec() and sp_executeSQL.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 6, 2012 at 8:32 am
Goodness me! Thank you so much for so detailed reply.
I changed varchar to nvarchar for @sql and @due, it throws me more errors:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'due'.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Due".
November 6, 2012 at 8:37 am
If you don't mind, here is the sp, thank you very much.
USE [Pergamum]
GO
/****** Object: StoredProcedure [dbo].[spGetDeviationReportNew2] Script Date: 11/06/2012 09:54:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Stored Procedure
ALTER PROCEDURE [dbo].[spGetDeviationReportNew2] (@ITSDivisionID int, @Due nvarchar(20))
AS
declare @OverDue nvarchar(20)
declare @DueIn30Days nvarchar(255)
declare @DueIn60Days nvarchar(255)
declare @DueIn90Days nvarchar(255)
declare @DueIn180Days nvarchar(255)
set @OverDue = '999'
set @DueIn30Days = '30'
set @DueIn60Days = '60'
set @DueIn90Days = '90'
set @DueIn180Days = '180'
declare @sql nvarchar(1000)
set @sql = 'Select DocID, Code, Ref, Title, DocType, FileName, InitialSignOffDate, LastReviewDate, NextReviewDate, '+
' Due = Replace(@Due, ' + '''' + 'in' + '''' + ', ' + '''' + 'Within' + '''' + '), DueValue, SA, Type, Category, '+
' Division, ITSDivision, Contact, Phone) From ##temp where DueValue <= '
if (@Due = 'All') set @sql = 'Select * From ##temp'
if (@Due = 'OverDue') set @sql = @sql + @OverDue
if (@Due = 'Due In 30 Days') set @sql = @sql + @DueIn30Days
if (@Due = 'Due In 60 Days') set @sql = @sql + @DueIn60Days
if (@Due = 'Due In 90 Days') set @sql = @sql + @DueIn90Days
if (@Due = 'Due In 180 Days') set @sql = @sql + @DueIn180Days
else
Select ITSDivision = 'All', Due = @Due
End
ELSE
--exec (@sql)
EXEC sp_executeSQL @sql, @Due
drop table ##temp
November 6, 2012 at 8:37 am
That's from the missing "list the parameters" piece that I mentioned. Look at the samples, you'll see how it goes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 6, 2012 at 8:52 am
Thank you, I will try to fix it myself
November 6, 2012 at 9:39 am
Try replacing this:
EXEC sp_executeSQL @sql, @Due
With:
EXEC sp_executeSQL @sql, N'@Due NVARCHAR(20)', @Due
See if that does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 7, 2012 at 11:18 am
When your code reaches the statement
if (@Due = 'All') set @sql = 'Select * From ##temp'
It is realising that @due has not been declared. The '@due' in the string above it is not recognized as a variable
----------------------------------------------------
November 7, 2012 at 12:12 pm
mmartin1 (11/7/2012)
When your code reaches the statement
if (@Due = 'All') set @sql = 'Select * From ##temp'It is realising that @due has not been declared. The '@due' in the string above it is not recognized as a variable
@Due is declared as an input parameter in the usual manner for procs. It's in-scope at that stage of the code. Where it's not "declared" is inside the dynamic SQL string.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2012 at 10:41 am
Ok thanks for the note, I missed that. I guess haste does make waste.
----------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply