August 7, 2012 at 10:06 am
I have a simple SPoc:
ALTER PROCEDURE [dbo].[TestSPR]
@P1 int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MyExec VARCHAR(100)
SET @MyExec = 'Select * from MyTable where MyField != @p1'
Exec (@MyExec)
END
But it gives Why Must Declare a Scalar variable when I run
If I take away the MYExec and just run the code, it runs fine
What Gives ?
Above is a very simple example of a SProc where I have 10 parameters
August 7, 2012 at 10:11 am
Because you didn't define your scalar inside your string.
The bad part of this is that you receive @p1 as a parameter and then you execute it. That is probably the most dangerous thing you can do in sql server.
You should make use of parameters in your dynamic sql too.
SET @MyExec = 'Select * from MyTable where MyField != @p1'
EXEC sp_executesql @MyExec, N'@p1 int', @p1
_______________________________________________________________
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/
August 7, 2012 at 10:11 am
The declaration of @MyExec goes out of scope inside your EXEC (@MyExec).
If you want to pass variables to/from a dynamic SQL batch, use SP_EXECUTESQL.
August 7, 2012 at 10:12 am
You need to pass the parameter to the sql.
I think this will work:
ALTER PROCEDURE [dbo].[TestSPR]
@P1 int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MyExec VARCHAR(100)
SET @MyExec = 'Select * from MyTable where MyField != @p1'
--Exec (@MyExec)
EXEC sp_executesql @MyExec, N'@p1 int', @p1
END
August 7, 2012 at 10:22 am
Hi Thanks for your reply
So, I gather then that ypu need to mention the Parameter twice ?
Once just after the Procure name
....ALTER PROCEDURE [dbo].[TestSPR]
... @P1 int
and again in the ExecSql line
EXEC sp_executesql @MyExec, N'@p1 int', @p1
(Looks like the parameter is getting defined again )
If I have 10 or 20 parameters, this looks like a lot of duplication, when really there should be no need for it
Who devises these database Systems anyway ??
August 7, 2012 at 10:22 am
laurie-789651 (8/7/2012)
You need to pass the parameter to the sql.I think this will work:
ALTER PROCEDURE [dbo].[TestSPR]
@P1 int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MyExec VARCHAR(100)
SET @MyExec = 'Select * from MyTable where MyField != @p1'
--Exec (@MyExec)
EXEC sp_executesql @MyExec, N'@p1 int', @p1
END
I think @MyExec has to be defined as NVARCHAR.
August 7, 2012 at 10:27 am
gerard-593414 (8/7/2012)
I have a simple SPoc:ALTER PROCEDURE [dbo].[TestSPR]
@P1 int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MyExec VARCHAR(100)
SET @MyExec = 'Select * from MyTable where MyField != @p1'
Exec (@MyExec)
END
But it gives Why Must Declare a Scalar variable when I run
If I take away the MYExec and just run the code, it runs fine
What Gives ?
Above is a very simple example of a SProc where I have 10 parameters
Why are you using dnamic sql here any way?
ALTER PROCEDURE [dbo].[TestSPR]
@P1 int
AS
BEGIN
SET NOCOUNT ON;
Select
* -- Really should explicitly identify the columns used in the SELECT
from
MyTable
where
MyField != @p1;
END
August 7, 2012 at 11:41 am
gerard-593414 (8/7/2012)
Hi Thanks for your replySo, I gather then that ypu need to mention the Parameter twice ?
Once just after the Procure name
....ALTER PROCEDURE [dbo].[TestSPR]
... @P1 int
and again in the ExecSql line
EXEC sp_executesql @MyExec, N'@p1 int', @p1
(Looks like the parameter is getting defined again )
If I have 10 or 20 parameters, this looks like a lot of duplication, when really there should be no need for it
Who devises these database Systems anyway ??
You are in fact declaring them again. That is because they are now in your dynamic sql. The other option is to run your code totally vulnerable to sql injection OR do as Lynn suggested and drop the dynamic portion of this entirely. From what you posted there is no need for dynamic sql at all.
_______________________________________________________________
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/
August 7, 2012 at 1:16 pm
Hy Lynn. Thanks for replying.
The sample I gave ws just a very simple sample to illustrate the error.
There are in fact 8 parameters input as :
@Type, @WhichFieldQuery,@CustomerFrom, @CustomerTo, @TranFrom,@TranTo, @dateFrom,@DateTo
The reason I need Dynamic Sql is because I need to build up the select as:
If @Type = 'G', then Where is '>='
If @Type = 'L' then Where is '<='
If @Type = 'E', then Where is '='
@WhichFieldToQuery
If @WhichFieldToQuery = 'INV' then FieldToQuery = 'where INVNUM'
If @WhichFieldToQuery = 'DES' then FieldToQuery = 'where Despnum'
If @WhcihFieldToQuery = 'ORD' then FieldToQuery = 'where OrderNum'
if @CustomerFrom is Blank, no need for a Select where Acnum >=, esle need 'and Cusnum >= @Customerfrom
Etc Etc
The only way I know of running the Select is to build up the string but is there a better /different way ?
August 7, 2012 at 1:26 pm
gerard-593414 (8/7/2012)
Hy Lynn. Thanks for replying.The sample I gave ws just a very simple sample to illustrate the error.
There are in fact 8 parameters input as :
@Type, @WhichFieldQuery,@CustomerFrom, @CustomerTo, @TranFrom,@TranTo, @dateFrom,@DateTo
The reason I need Dynamic Sql is because I need to build up the select as:
If @Type = 'G', then Where is '>='
If @Type = 'L' then Where is '<='
If @Type = 'E', then Where is '='
@WhichFieldToQuery
If @WhichFieldToQuery = 'INV' then FieldToQuery = 'where INVNUM'
If @WhichFieldToQuery = 'DES' then FieldToQuery = 'where Despnum'
If @WhcihFieldToQuery = 'ORD' then FieldToQuery = 'where OrderNum'
if @CustomerFrom is Blank, no need for a Select where Acnum >=, esle need 'and Cusnum >= @Customerfrom
Etc Etc
The only way I know of running the Select is to build up the string but is there a better /different way ?
That is the problem with simplifying your problem to the point you did. Based on the original post, there was no reason to use dynamic sql. this is one of the reasons people need to provide as much information as possible when posting questions. It prevents misunderstandings regarding the requirements that need to be met.
Based on the information provided now, it would be possibile to define a switchboard procedure that would then call the appropriate child procedure that has the specifically defined query that needs to be run to meet the requirements based on the parameters based to the parent procedure.
Again, it isn't necessary to use dynamic sql. There are times it is appropriate and it depending on the number of parameters and combination of possible parameters it may be best rather than trying to support 10's or hundreds of fixed stored procedures.
If you are using dynamic sql, there is additional overhead in its development and processing. One of those is the multiple declaration of parameters. The other is writing the code to prevent or limit the opportunities for SQL Injection.
August 7, 2012 at 1:36 pm
You might also take a look at Gail's blog post about "catch all" queries. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
She does a great job of explaining some options and how best to code for scenarios like this one.
_______________________________________________________________
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/
August 7, 2012 at 5:45 pm
Sean Lange (8/7/2012)
You might also take a look at Gail's blog post about "catch all" queries. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]She does a great job of explaining some options and how best to code for scenarios like this one.
I definitely second that.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply