asked
I am trying to use sp_ExecuteSql in my stored procedure to get a count of numbers 1 to 10 for different filter options. I unable to get the desired result. Please let me know if my implementation is correct
My input parameters are multi-valued parameters EXEC proc_Scores 'tier 2', '2013'',''2014', 'North America ', 'USA ' CREATE PROC proc__Scores @tier NVARCHAR(20) , @year NVARCHAR(20) , @subregion NVARCHAR(100) , @country NVARCHAR(100) = NULL AS BEGIN SET nocount ON DECLARE @choice INT , @sql NVARCHAR(MAX) , @tbl_name NVARCHAR(255) , @question NVARCHAR(255) SET @choice = 0 SET @question = 'Q3' SET @tbl_name = ( SELECT tier_tbl_name FROM tbl_tier_tblname_qns WHERE tiername = @tier ) WHILE ( @choice <= 10 ) BEGIN SET @sql = N' select tier, yr' + ', Q3 ,' + ' @chc ,count( @chc ) from ' + @tbl_name + ' where 1=1 ' SET @sql += CASE WHEN @year IS NOT NULL THEN N' and yr in ( @yr )' ELSE '' END + CASE WHEN @subregion IS NOT NULL THEN N' and Sreg in ( @subr ) ' ELSE '' END + CASE WHEN @country IS NOT NULL THEN N' and country in ( @cty ) ' ELSE '' END + N' and Q3 = @chc' + N' group by tier, yr, Q3' DECLARE @ParmDefinition NVARCHAR(2000) SET @ParmDefinition = N'@yr nvarchar(20), @subr nvarchar(100), @cty nvarchar(100), @chc int' EXEC sp_executesql @SQL, @ParmDefinition, @yr = @year, @subr = @subregion, @cty = @country, @chc = @choice SET @choice = @choice + 1 END END The result should be like this: Tier Year Q3 Choices Count_of_choices Tier 1 2013 Q3 0 1 Tier 1 2013 Q3 1 1 Tier 1 2013 Q3 2 1 Tier 1 2013 Q3 5 8 Tier 1 2013 Q3 6 3 Tier 1 2013 Q3 7 5 I read that, to use sp_ExecuteSql I need to convert even the parameters in the dynamic sql string to a string. But after doing it I receive the err Error Message: Must declare the table variable @tbl_name Please let me know if I have used sp_ExecuteSql properly. Thanks, Suj The below code is the actual code that I am trying to change CREATE PROC proc__ScoresOld @tier NVARCHAR(20) , @year NVARCHAR(20) , @subregion NVARCHAR(100) , @country NVARCHAR(100) = NULL AS BEGIN SET nocount ON DECLARE @choice INT , @sql NVARCHAR(MAX) , @tbl_name NVARCHAR(255) , @question NVARCHAR(255) SET @choice = 0 SET @question = 'Q3' SET @tbl_name = ( SELECT tier_tbl_name FROM tbl_tier_tblname_qns WHERE tiername = @tier ) WHILE ( @choice <= 10 ) BEGIN SET @sql = N' select tier, yr ' + ',' + '''' + @question + '''' + ' as Question ,' + 'convert(int,' + @choice + ' ),' + 'count(convert(int,' + @choice + ' )) from ' + @tbl_name + ' where 1=1 ' SET @sql += CASE WHEN @year IS NOT NULL THEN N' and yr in (' + '''' + @year + '''' + ') ' ELSE '' END + CASE WHEN @subregion IS NOT NULL THEN N' and Sreg in (' + '''' + @subregion + '''' + ') ' ELSE '' END + CASE WHEN @country IS NOT NULL THEN N' and country in (' + '''' + @country + '''' + ') ' ELSE '' END + N' and ' + @question + ' = ' + @choice + +N' group by tier, yr,' + @question INSERT INTO tbl_result EXEC ( @sql ) SET @choice = @choice + 1 END SELECT * FROM tbl_result END
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
In your first section of code (proc__Scores), you've not concatenated in the value for @tbl_name: SET @sql = N' select tier, yr' + ', Q3 ,' + ' @chc ,count( @chc ) from @tbl_name where 1=1 ' Sh Should be: SET @sql = N' select tier, yr' + ', Q3 ,' + ' @chc ,count( @chc ) from ' + @tbl_name + ' where 1=1 ' So the query passed into sp_executesql is "select ... from @tbl_name", not "select ... from TheActualTable" But in your second (proc__ScoresOLD) you have though. From looking at your this, does it not throw a conversion error on trying to concatenate @choice without casting it to a varchar?
Hi, iainrobertson, thanks for your comment. Yes, I have modified as you said and now my query runs and the second dynamic sql returns something like this: SELECT tier , yr , Q3 , @chc , COUNT(@chc) FROM tbl_scores WHERE 1 = 1 AND yr IN ( @yr ) AND Sreg IN ( @subr ) AND country IN ( @cty ) AND Q3 = @chc GROUP BY tier , yr , Q3 Now, I am not sure how to pass the values to these parameters through sp_executeSql
Sorry to take a while to respond, I've had a busy few days. Passing lists of values into sp_executesql via parameters is a bit fernickity. You can't simply pass in a string, as this is interpreted as a whole, rather than split into its parts. Unfortunately, you can't just pass a table variable in either, this just throws a syntax error. So you have to declare custom types and then declare and pass objects using these types. Here's an example: create table #MyTable (Id int) go insert #MyTable values (1),(2),(3) go -- drop types if they already exist if exists (select 'type exists' from sys.types where name = 'tv_tbl1_type') begin drop type tv_tbl1_type end if exists (select 'type exists' from sys.types where name = 'tv_tbl2_type') begin drop type tv_tbl2_type end -- create table types create type tv_tbl1_type as table (Id int) create type tv_tbl2_type as table (Id int) go -- declare new objects of types created above declare @tbl_1 tv_tbl1_type declare @tbl_2 tv_tbl2_type -- populate with values insert @tbl_1 values (1),(2) insert @tbl_2 values (2),(3) -- create sp_executesql inputs declare @sql nvarchar(2000) , @params nvarchar(2000) set @sql = N'select * from #MyTable where Id in (select Id from @tbl_1) and Id in (select Id from @tbl_2)' set @params = N'@tbl_1 tv_tbl1_type readonly, @tbl_2 tv_tbl2_type readonly ' -- execute - note passing in of table at end of paramter list exec sp_executesql @sql, @params, @tbl_1, @tbl_2 Of course, if you're going to the bother of splitting the data into elements and putting them into tables, you could just use temp tables. These are available to sp_executesql anyway: -- populate temp tables instead create table #tbl_1 (Id int) create table #tbl_2 (Id int) insert #tbl_1 values (1),(2) insert #tbl_2 values (2),(3) -- create sp_executesql inputs declare @sql nvarchar(2000) , @params nvarchar(2000) set @sql = N'select * from #MyTable where Id in (select Id from #tbl_1) and Id in (select Id from #tbl_2)' -- execute exec sp_executesql @sql Which is a lot less hassle.
16 People are following this question.
