February 23, 2014 at 6:43 am
Hello All,
I'm very new to sql this is my first attempt, I am trying to use cursor with dynamic sql to identify the column name to insert values into, the problem I am currently hitting is:
"Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@col"."
Although I thought I had already defined this at the top as "DECLARE @col NVARCHAR(10);" so I don't really understand the error.
Thanks
Full Error:
Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@col".
2e14
MON2
Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@col".
c827
MON2
Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@col".
e7ef
MON2
-------------
DECLARE @MONTHP INT
DECLARE @REPORTEDMONTHS VARCHAR(100)
DECLARE @PLUSONE INT
DECLARE @NAMECOLUMN VARCHAR(3)
DECLARE @sql NVARCHAR(2000);
DECLARE @col NVARCHAR(10);
--Cursor
DECLARE @MyCursor CURSOR
DECLARE @APP_ID VARCHAR(100)
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT APP_ID, MONTHP, REPORTEDMONTHS
FROM TEMPMONTHLYMATCHES_REPORTEDMONTHS
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @APP_ID,@MONTHP,@REPORTEDMONTHS
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PLUSONE = @REPORTEDMONTHS+1
SET @NAMECOLUMN = 'MON'
SET @col = (SELECT CONVERT(varchar(5), @NAMECOLUMN) + CONVERT(varchar(5), @PLUSONE))
SET @sql = REPLACE(@sql, N'@col', @col);
--DYNAMIC SQL PART
set @sql =
N'
UPDATE FY08_ALL
SET @col = "@MONTHP"
WHERE APP_ID = "@APP_ID";
';
EXEC(@sql);
PRINT @APP_ID
PRINT @COL
FETCH NEXT FROM @MyCursor
INTO @APP_ID,@MONTHP,@REPORTEDMONTHS
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
February 23, 2014 at 10:24 am
bobyahed2dis (2/23/2014)
Hello All,I'm very new to sql this is my first attempt, I am trying to use cursor with dynamic sql to identify the column name to insert values into, the problem I am currently hitting is:
"Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@col"."
Although I thought I had already defined this at the top as "DECLARE @col NVARCHAR(10);" so I don't really understand the error.
Thanks
Full Error:
Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@col".
2e14
MON2
Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@col".
c827
MON2
Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@col".
e7ef
MON2
-------------
DECLARE @MONTHP INT
DECLARE @REPORTEDMONTHS VARCHAR(100)
DECLARE @PLUSONE INT
DECLARE @NAMECOLUMN VARCHAR(3)
DECLARE @sql NVARCHAR(2000);
DECLARE @col NVARCHAR(10);
--Cursor
DECLARE @MyCursor CURSOR
DECLARE @APP_ID VARCHAR(100)
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT APP_ID, MONTHP, REPORTEDMONTHS
FROM TEMPMONTHLYMATCHES_REPORTEDMONTHS
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @APP_ID,@MONTHP,@REPORTEDMONTHS
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PLUSONE = @REPORTEDMONTHS+1
SET @NAMECOLUMN = 'MON'
SET @col = (SELECT CONVERT(varchar(5), @NAMECOLUMN) + CONVERT(varchar(5), @PLUSONE))
SET @sql = REPLACE(@sql, N'@col', @col);
--DYNAMIC SQL PART
set @sql =
N'
UPDATE FY08_ALL
SET @col = "@MONTHP"
WHERE APP_ID = "@APP_ID";
';
EXEC(@sql);
PRINT @APP_ID
PRINT @COL
FETCH NEXT FROM @MyCursor
INTO @APP_ID,@MONTHP,@REPORTEDMONTHS
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
Firstly don't use a cursor for this. you need values from the table TEMPMONTHLYMATCHES_REPORTEDMONTHS correct? The app_id seems to be a common key between the 2 tables. Can you post the definitions for both tables.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 23, 2014 at 10:52 am
Jim Arko (2/23/2014)
When using dynamic T-SQL you need to concatenate the variables.
SET @sqlstring = 'UPDATE table SET column = ' + @variable
Sure, if you want a nice little SQL Injection vulnerability. Assuming you care an iota about security, you wouldn't concatenate variables into a string and execute it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2014 at 11:25 am
It's certainly not best practice, but the question was about variables in dynamic T-SQL. The link provided addresses injection. There is probably an entirely better way to accomplish the end goal as Perry suggests but we do not have the table definitions.
February 23, 2014 at 12:01 pm
Hello All,
thanks for coming back to my post,
i'm using the cursor as i need to cycle through the records one by one
TEMPMONTHLYMATCHES_REPORTEDMONTHS contains the following;
APP_ID = unique serial that joins the monthly tables
MONTHP = contains the value that i want to insert into the table FY08_all by each APP_ID
REPORTEDMONTHS = the number of months that serial has reported so if its reported 1 month in the FY08_all table I want the new value to end up in column "MON2" so in this example I am adding +1 here:
SET @PLUSONE = @REPORTEDMONTHS+1
SET @NAMECOLUMN = 'MON'
SET @col = (SELECT CONVERT(varchar(5), @NAMECOLUMN) + CONVERT(varchar(5), @PLUSONE))
The problem with the data I have is that its in monthly batches in single month tables that don't have the dates so I have to assume the first months is the first reported month (MON1, in FY08_ALL), the @monthp need to be entered into various months of the FY08_ALL table depending on how many months the APP_ID has reported that's why I need to use dynamic sql to generate the target column in FY08_ALL.
Security is a secondary concern as its hosted locally on my computer if it can be maintained great! If there's a better way to achieve please advise as I'm just starting out!
Thanks again, I hope that clarifies some of the questions.
February 23, 2014 at 12:30 pm
bobyahed2dis (2/23/2014)
Hello All,thanks for coming back to my post,
i'm using the cursor as i need to cycle through the records one by one
TEMPMONTHLYMATCHES_REPORTEDMONTHS contains the following;
APP_ID = unique serial that joins the monthly tables
MONTHP = contains the value that i want to insert into the table FY08_all by each APP_ID
REPORTEDMONTHS = the number of months that serial has reported so if its reported 1 month in the FY08_all table I want the new value to end up in column "MON2" so in this example I am adding +1 here:
SET @PLUSONE = @REPORTEDMONTHS+1
SET @NAMECOLUMN = 'MON'
SET @col = (SELECT CONVERT(varchar(5), @NAMECOLUMN) + CONVERT(varchar(5), @PLUSONE))
The problem with the data I have is that its in monthly batches in single month tables that don't have the dates so I have to assume the first months is the first reported month (MON1, in FY08_ALL), the @monthp need to be entered into various months of the FY08_ALL table depending on how many months the APP_ID has reported that's why I need to use dynamic sql to generate the target column in FY08_ALL.
Security is a secondary concern as its hosted locally on my computer if it can be maintained great! If there's a better way to achieve please advise as I'm just starting out!
Thanks again, I hope that clarifies some of the questions.
The update itself can be condensed as follows and doesn't require the cursor
UPDATE b
SET b.col = a.MONTHP
FROM [FY08_ALL] b INNER JOIN [TEMPMONTHLYMATCHES_REPORTEDMONTHS] a
ON b.app_id = a.app_id
As for printing the results of the variable column by column this too could be handled without the cursor and condensed down.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 23, 2014 at 1:23 pm
Hi,
How does this know what MON* column to put into as that is the value that is changing:
SET @PLUSONE = @REPORTEDMONTHS+1
SET @NAMECOLUMN = 'MON'
SET @col = (SELECT CONVERT(varchar(5), @NAMECOLUMN) + CONVERT(varchar(5), @PLUSONE))
@col which will be something like "MON2" or "MON3", the table TEMPMONTHLYMATCHES_REPORTEDMONTHS only contains the counted number of reported columns a particular app_id has reported. Which is why @plusone adds +1 to @REPORTEDMONTHS+1 so it puts the data in the next column when joined to MON?
The FY08_ALL tables values are MON1,MON2,MON3 etc, so the string is being created to target the specific columns, is this what your answer is doing? sorry i'm just trying to understand.
Thanks for your time.
February 23, 2014 at 2:30 pm
I continued with my previous method until someone could give clarity on the above. Working with the message relating to converting as a string I now have a different error. I understand I need to use a cast or convert but I can't figure out how.
I changed this from the above:
set @sql =
'UPDATE FY08_ALL SET ' + @col + ' = '+ CAST(@MONTHP as int) + ' WHERE APP_ID = '+ @APP_ID +''
I now get the error:
Msg 245, Level 16, State 1, Line 27
Conversion failed when converting the varchar value 'UPDATE FY08_ALL SET MON2 = ' to data type int.
DECLARE @MONTHP INT
DECLARE @REPORTEDMONTHS VARCHAR(100)
DECLARE @PLUSONE INT
DECLARE @NAMECOLUMN VARCHAR(30)
DECLARE @sql NVARCHAR(max);
DECLARE @col NVARCHAR(10);
--Cursor
DECLARE @MyCursor CURSOR
DECLARE @APP_ID VARCHAR(100)
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT APP_ID, MONTHP, REPORTEDMONTHS
FROM TEMPMONTHLYMATCHES_REPORTEDMONTHS
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @APP_ID,@MONTHP,@REPORTEDMONTHS
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PLUSONE = @REPORTEDMONTHS+1
SET @NAMECOLUMN = 'MON'
SET @col = (SELECT CONVERT(varchar(5), @NAMECOLUMN) + CONVERT(varchar(5), @PLUSONE))
--not WORKING BELOW
set @sql =
'UPDATE FY08_ALL SET ' + @col + ' = '+ CAST(@MONTHP as int) + ' WHERE APP_ID = '+ @APP_ID +''
EXEC @sql
PRINT @APP_ID
PRINT @COL
FETCH NEXT FROM @MyCursor
INTO @APP_ID,@MONTHP,@REPORTEDMONTHS
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
February 24, 2014 at 8:58 am
GilaMonster (2/23/2014)
Jim Arko (2/23/2014)
When using dynamic T-SQL you need to concatenate the variables.
SET @sqlstring = 'UPDATE table SET column = ' + @variableSure, if you want a nice little SQL Injection vulnerability. Assuming you care an iota about security, you wouldn't concatenate variables into a string and execute it.
I don't mean to hijack but I'm curious whether this would aleviate concerns:
SET @sqlstring = 'UPDATE table SET column = ' + QUOTENAME(@variable)
Is this still injectable? (I couldn't do the semicolon append hack for instance once I used QUOTENAME)
February 24, 2014 at 9:18 am
patrickmcginnis59 10839 (2/24/2014)
GilaMonster (2/23/2014)
Jim Arko (2/23/2014)
When using dynamic T-SQL you need to concatenate the variables.
SET @sqlstring = 'UPDATE table SET column = ' + @variableSure, if you want a nice little SQL Injection vulnerability. Assuming you care an iota about security, you wouldn't concatenate variables into a string and execute it.
I don't mean to hijack but I'm curious whether this would aleviate concerns:
SET @sqlstring = 'UPDATE table SET column = ' + QUOTENAME(@variable)Is this still injectable? (I couldn't do the semicolon append hack for instance once I used QUOTENAME)
Take a look here. This is pretty much the definitive article about dynamic sql.
http://www.sommarskog.se/dynamic_sql.html#quotestring
_______________________________________________________________
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/
February 24, 2014 at 9:18 am
You seem to have a CROSS TABS or PIVOT problem that you're trying to solve using a CURSOR :sick: .
Reviewing your code, you're adding an integer to a varchar and that might be a problem.
Check this articles about CROSS TABS to see if it's what you need.
February 24, 2014 at 9:39 am
Sean Lange (2/24/2014)
patrickmcginnis59 10839 (2/24/2014)
GilaMonster (2/23/2014)
Jim Arko (2/23/2014)
When using dynamic T-SQL you need to concatenate the variables.
SET @sqlstring = 'UPDATE table SET column = ' + @variableSure, if you want a nice little SQL Injection vulnerability. Assuming you care an iota about security, you wouldn't concatenate variables into a string and execute it.
I don't mean to hijack but I'm curious whether this would aleviate concerns:
SET @sqlstring = 'UPDATE table SET column = ' + QUOTENAME(@variable)Is this still injectable? (I couldn't do the semicolon append hack for instance once I used QUOTENAME)
Take a look here. This is pretty much the definitive article about dynamic sql.
Well I guess if Erland doesn't know, I'm guessing nobody here would know either. Consider my question invalid!
edit: http://www.sqlskills.com/blogs/kimberly/little-bobby-tables-sql-injection-and-execute-as/ discusses quotename a bit also.
edit #2: What I like about QUOTENAME is that while it defaults to surrounding the column name with '[' and ']' (which I'm figuring means the quote char is configurable), it ALSO escapes any embedded ]'s to avoid injection.
February 24, 2014 at 10:27 am
patrickmcginnis59 10839 (2/24/2014)
Sean Lange (2/24/2014)
patrickmcginnis59 10839 (2/24/2014)
GilaMonster (2/23/2014)
Jim Arko (2/23/2014)
When using dynamic T-SQL you need to concatenate the variables.
SET @sqlstring = 'UPDATE table SET column = ' + @variableSure, if you want a nice little SQL Injection vulnerability. Assuming you care an iota about security, you wouldn't concatenate variables into a string and execute it.
I don't mean to hijack but I'm curious whether this would aleviate concerns:
SET @sqlstring = 'UPDATE table SET column = ' + QUOTENAME(@variable)Is this still injectable? (I couldn't do the semicolon append hack for instance once I used QUOTENAME)
Take a look here. This is pretty much the definitive article about dynamic sql.
Well I guess if Erland doesn't know, I'm guessing nobody here would know either. Consider my question invalid!
edit: http://www.sqlskills.com/blogs/kimberly/little-bobby-tables-sql-injection-and-execute-as/ discusses quotename a bit also.
edit #2: What I like about QUOTENAME is that while it defaults to surrounding the column name with '[' and ']' (which I'm figuring means the quote char is configurable), it ALSO escapes any embedded ]'s to avoid injection.
QUOTENAME is one way to deal with injection. To me it seems like the wrong tool for the job though. It is a lot easier to parameterize queries (even better as a stored proc) than to have to wrap everything in QUOTENAME.
_______________________________________________________________
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/
February 24, 2014 at 10:56 am
Sean Lange (2/24/2014)
patrickmcginnis59 10839 (2/24/2014)
Sean Lange (2/24/2014)
patrickmcginnis59 10839 (2/24/2014)
GilaMonster (2/23/2014)
Jim Arko (2/23/2014)
When using dynamic T-SQL you need to concatenate the variables.
SET @sqlstring = 'UPDATE table SET column = ' + @variableSure, if you want a nice little SQL Injection vulnerability. Assuming you care an iota about security, you wouldn't concatenate variables into a string and execute it.
I don't mean to hijack but I'm curious whether this would aleviate concerns:
SET @sqlstring = 'UPDATE table SET column = ' + QUOTENAME(@variable)Is this still injectable? (I couldn't do the semicolon append hack for instance once I used QUOTENAME)
Take a look here. This is pretty much the definitive article about dynamic sql.
Well I guess if Erland doesn't know, I'm guessing nobody here would know either. Consider my question invalid!
edit: http://www.sqlskills.com/blogs/kimberly/little-bobby-tables-sql-injection-and-execute-as/ discusses quotename a bit also.
edit #2: What I like about QUOTENAME is that while it defaults to surrounding the column name with '[' and ']' (which I'm figuring means the quote char is configurable), it ALSO escapes any embedded ]'s to avoid injection.
QUOTENAME is one way to deal with injection. To me it seems like the wrong tool for the job though. It is a lot easier to parameterize queries (even better as a stored proc) than to have to wrap everything in QUOTENAME.
IMO, QUOTENAME is useful to wrap dynamic columns or object names. Parametrized queries should still be used for values.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply