|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
local variable not recognized when executing dynamic sqlI have a stored proc something similar to this: CREATE PROC spGetApplesAndMangoes @units int AS Declare @dynSQL nvarchar(500) Declare @apples int, @mangoes int -- contruct this dynamic sql statement using conditions etc. SET @dynSQL = "Select @apples = no_of_apples, @mangoes = no_of_mangoes From MyFruitFarm" IF .... > ... -- Some condition SET @dynSQL = @dynSQL = " ............ .. .. .. .. ." -- execute the dynamic sql statement exec sp_executesql @dynSQL print @apples print @mangoes GO Above procedure when executed throws an error saying the local variables @apples and @mangoes are not declared and needs to be declared before they are used. This was weird at first but after spending some hours I now know why it complains about the variables not being declared. Its because the proc is executing a dynamic statement and expects those variables to be part of the dynamic sql statement. Can you guys recommend what is the best way to retrieve values of apples and mangoes in local variables that I can make use of elsewhere in the procedure Thanks in advance ... :-) The first question to ask in a situation like this is:
What is happening in the [--Some condition] section that absolutely requires this to be dynamic SQL? It may be possible to create the conditional code without dynamic SQL. -- Show quoteHide quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "dotnet dude" <arpit***@gmail.com> wrote in message news:1157988123.867104.31120@i3g2000cwc.googlegroups.com... > This is the issue I am facing: > > I have a stored proc something similar to this: > > CREATE PROC spGetApplesAndMangoes > @units int > AS > > Declare @dynSQL nvarchar(500) > Declare @apples int, @mangoes int > > -- contruct this dynamic sql statement using conditions etc. > SET @dynSQL = "Select @apples = no_of_apples, @mangoes = > no_of_mangoes > From MyFruitFarm" > > IF .... > ... -- Some condition > SET @dynSQL = @dynSQL = " ............ .. .. .. .. ." > > -- execute the dynamic sql statement > exec sp_executesql @dynSQL > > print @apples > print @mangoes > > GO > > Above procedure when executed throws an error saying the local > variables @apples and @mangoes are not declared and needs to be > declared before they are used. > > This was weird at first but after spending some hours I now know why it > complains about the variables not being declared. Its because the proc > is executing a dynamic statement and expects those variables to be part > of the dynamic sql statement. > > Can you guys recommend what is the best way to retrieve values of > apples and mangoes in local variables that I can make use of elsewhere > in the procedure > > Thanks in advance ... :-) > This is hard to explain since the stored proc implements a complex
business logic that I wont be able to describe here ( confidentiality constraint :-D ). Basically, it creates a dynamic sql adding new where conditions, new joins and group bys. Each condition is checked by the CASE and IF statements. Arnie Rowland wrote: Show quoteHide quote > The first question to ask in a situation like this is: > > What is happening in the [--Some condition] section that absolutely requires > this to be dynamic SQL? > > It may be possible to create the conditional code without dynamic SQL. > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "dotnet dude" <arpit***@gmail.com> wrote in message > news:1157988123.867104.31120@i3g2000cwc.googlegroups.com... > > This is the issue I am facing: > > > > I have a stored proc something similar to this: > > > > CREATE PROC spGetApplesAndMangoes > > @units int > > AS > > > > Declare @dynSQL nvarchar(500) > > Declare @apples int, @mangoes int > > > > -- contruct this dynamic sql statement using conditions etc. > > SET @dynSQL = "Select @apples = no_of_apples, @mangoes = > > no_of_mangoes > > From MyFruitFarm" > > > > IF .... > ... -- Some condition > > SET @dynSQL = @dynSQL = " ............ .. .. .. .. ." > > > > -- execute the dynamic sql statement > > exec sp_executesql @dynSQL > > > > print @apples > > print @mangoes > > > > GO > > > > Above procedure when executed throws an error saying the local > > variables @apples and @mangoes are not declared and needs to be > > declared before they are used. > > > > This was weird at first but after spending some hours I now know why it > > complains about the variables not being declared. Its because the proc > > is executing a dynamic statement and expects those variables to be part > > of the dynamic sql statement. > > > > Can you guys recommend what is the best way to retrieve values of > > apples and mangoes in local variables that I can make use of elsewhere > > in the procedure > > > > Thanks in advance ... :-) > > 'nuf said.
You will need to pass in references to the variables as part of the dynamic SQL statement. For a good primer on using dynamic SQL, see Erland's article: http://www.sommarskog.se/dynamic_sql.html -- Show quoteHide quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "dotnet dude" <arpit***@gmail.com> wrote in message news:1157999112.819656.55960@m73g2000cwd.googlegroups.com... > This is hard to explain since the stored proc implements a complex > business logic that I wont be able to describe here ( confidentiality > constraint :-D ). > Basically, it creates a dynamic sql adding new where conditions, new > joins and group bys. Each condition is checked by the CASE and IF > statements. > > Arnie Rowland wrote: >> The first question to ask in a situation like this is: >> >> What is happening in the [--Some condition] section that absolutely >> requires >> this to be dynamic SQL? >> >> It may be possible to create the conditional code without dynamic SQL. >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "dotnet dude" <arpit***@gmail.com> wrote in message >> news:1157988123.867104.31120@i3g2000cwc.googlegroups.com... >> > This is the issue I am facing: >> > >> > I have a stored proc something similar to this: >> > >> > CREATE PROC spGetApplesAndMangoes >> > @units int >> > AS >> > >> > Declare @dynSQL nvarchar(500) >> > Declare @apples int, @mangoes int >> > >> > -- contruct this dynamic sql statement using conditions etc. >> > SET @dynSQL = "Select @apples = no_of_apples, @mangoes = >> > no_of_mangoes >> > From MyFruitFarm" >> > >> > IF .... > ... -- Some condition >> > SET @dynSQL = @dynSQL = " ............ .. .. .. .. ." >> > >> > -- execute the dynamic sql statement >> > exec sp_executesql @dynSQL >> > >> > print @apples >> > print @mangoes >> > >> > GO >> > >> > Above procedure when executed throws an error saying the local >> > variables @apples and @mangoes are not declared and needs to be >> > declared before they are used. >> > >> > This was weird at first but after spending some hours I now know why it >> > complains about the variables not being declared. Its because the proc >> > is executing a dynamic statement and expects those variables to be part >> > of the dynamic sql statement. >> > >> > Can you guys recommend what is the best way to retrieve values of >> > apples and mangoes in local variables that I can make use of elsewhere >> > in the procedure >> > >> > Thanks in advance ... :-) >> > > You can use output parameters with sp_executesql:
Declare @dynSQL nvarchar(500) Declare @apples int, @mangoes int SET @dynSQL = 'Select @apples = no_of_apples, @mangoes = no_of_mangoes From MyFruitFarm' exec sp_executesql @dynSQL, N'@apples int output, @mangoes int output', @apples output, @mangoes output print @apples print @mangoes Dean Show quoteHide quote "dotnet dude" <arpit***@gmail.com> wrote in message news:1157999112.819656.55960@m73g2000cwd.googlegroups.com... > This is hard to explain since the stored proc implements a complex > business logic that I wont be able to describe here ( confidentiality > constraint :-D ). > Basically, it creates a dynamic sql adding new where conditions, new > joins and group bys. Each condition is checked by the CASE and IF > statements. > > Arnie Rowland wrote: >> The first question to ask in a situation like this is: >> >> What is happening in the [--Some condition] section that absolutely >> requires >> this to be dynamic SQL? >> >> It may be possible to create the conditional code without dynamic SQL. >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "dotnet dude" <arpit***@gmail.com> wrote in message >> news:1157988123.867104.31120@i3g2000cwc.googlegroups.com... >> > This is the issue I am facing: >> > >> > I have a stored proc something similar to this: >> > >> > CREATE PROC spGetApplesAndMangoes >> > @units int >> > AS >> > >> > Declare @dynSQL nvarchar(500) >> > Declare @apples int, @mangoes int >> > >> > -- contruct this dynamic sql statement using conditions etc. >> > SET @dynSQL = "Select @apples = no_of_apples, @mangoes = >> > no_of_mangoes >> > From MyFruitFarm" >> > >> > IF .... > ... -- Some condition >> > SET @dynSQL = @dynSQL = " ............ .. .. .. .. ." >> > >> > -- execute the dynamic sql statement >> > exec sp_executesql @dynSQL >> > >> > print @apples >> > print @mangoes >> > >> > GO >> > >> > Above procedure when executed throws an error saying the local >> > variables @apples and @mangoes are not declared and needs to be >> > declared before they are used. >> > >> > This was weird at first but after spending some hours I now know why it >> > complains about the variables not being declared. Its because the proc >> > is executing a dynamic statement and expects those variables to be part >> > of the dynamic sql statement. >> > >> > Can you guys recommend what is the best way to retrieve values of >> > apples and mangoes in local variables that I can make use of elsewhere >> > in the procedure >> > >> > Thanks in advance ... :-) >> > > Thanks Arnie and Thanks Dean
Have a good one Cheers Dean wrote: Show quoteHide quote > You can use output parameters with sp_executesql: > > Declare @dynSQL nvarchar(500) > Declare @apples int, @mangoes int > > SET @dynSQL = 'Select @apples = no_of_apples, @mangoes = no_of_mangoes From > MyFruitFarm' > > exec sp_executesql @dynSQL, N'@apples int output, @mangoes int output', > @apples output, @mangoes output > > print @apples > print @mangoes > > > Dean > > "dotnet dude" <arpit***@gmail.com> wrote in message > news:1157999112.819656.55960@m73g2000cwd.googlegroups.com... > > This is hard to explain since the stored proc implements a complex > > business logic that I wont be able to describe here ( confidentiality > > constraint :-D ). > > Basically, it creates a dynamic sql adding new where conditions, new > > joins and group bys. Each condition is checked by the CASE and IF > > statements. > > > > Arnie Rowland wrote: > >> The first question to ask in a situation like this is: > >> > >> What is happening in the [--Some condition] section that absolutely > >> requires > >> this to be dynamic SQL? > >> > >> It may be possible to create the conditional code without dynamic SQL. > >> > >> -- > >> Arnie Rowland, Ph.D. > >> Westwood Consulting, Inc > >> > >> Most good judgment comes from experience. > >> Most experience comes from bad judgment. > >> - Anonymous > >> > >> > >> "dotnet dude" <arpit***@gmail.com> wrote in message > >> news:1157988123.867104.31120@i3g2000cwc.googlegroups.com... > >> > This is the issue I am facing: > >> > > >> > I have a stored proc something similar to this: > >> > > >> > CREATE PROC spGetApplesAndMangoes > >> > @units int > >> > AS > >> > > >> > Declare @dynSQL nvarchar(500) > >> > Declare @apples int, @mangoes int > >> > > >> > -- contruct this dynamic sql statement using conditions etc. > >> > SET @dynSQL = "Select @apples = no_of_apples, @mangoes = > >> > no_of_mangoes > >> > From MyFruitFarm" > >> > > >> > IF .... > ... -- Some condition > >> > SET @dynSQL = @dynSQL = " ............ .. .. .. .. ." > >> > > >> > -- execute the dynamic sql statement > >> > exec sp_executesql @dynSQL > >> > > >> > print @apples > >> > print @mangoes > >> > > >> > GO > >> > > >> > Above procedure when executed throws an error saying the local > >> > variables @apples and @mangoes are not declared and needs to be > >> > declared before they are used. > >> > > >> > This was weird at first but after spending some hours I now know why it > >> > complains about the variables not being declared. Its because the proc > >> > is executing a dynamic statement and expects those variables to be part > >> > of the dynamic sql statement. > >> > > >> > Can you guys recommend what is the best way to retrieve values of > >> > apples and mangoes in local variables that I can make use of elsewhere > >> > in the procedure > >> > > >> > Thanks in advance ... :-) > >> > > > |
|||||||||||||||||||||||
Other interesting topics