| Author |
Topic |
|
writhe
Starting Member
15 Posts |
 Posted - 2002-07-26 : 08:37:58
|
| I've got a table with a bunch of columns named "Week1", "Week2", "Week3", "Week4", etc. After determining the week number, I need to update a specific column in the table.code:---------------------------------------------------------------------------------- @Week is varchar with a single number in it: 1, 2, 3, etc.-- @EntryID is input parameter-- append "Week" to @WeekSET @Week = 'Week' + @Week-- update pickSET @sql = 'UPDATE picks SET ' + @Week + ' = ' + @UserPick + ' WHERE EntryID = ' + @EntryIDEXEC @sql--------------------------------------------------------------------------------This is not working, though. I get this error message:code:--------------------------------------------------------------------------------Syntax error converting the varchar value 'UPDATE picks SET Week1 = ' to a column of data type int.--------------------------------------------------------------------------------The "Week1", "Week2", etc. columns are of the data type integer. I'm running SQL Server 2000.Can someone help me out with this? |
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-07-26 : 08:51:19
|
| When concatenating an int value to a string, you must cast it as a string.SET @Week = 'Week' + cast(@Week as char(1))This should fix the problem.Jeremy |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-26 : 08:52:54
|
| How big is your @sql variable?When you build the @sql string it looks like you need a space before the WHERE.Maybe you should look at normalizing your design.<O> |
|
|
|
writhe
Starting Member
15 Posts |
Posted - 2002-07-26 : 10:32:41
|
| Jeremy-I originally declared @Week as varchar(10), then put a number in it and then concatenated it with the text "Week". I think the concatenation is working fine.Page47-There's a space before the "where" clause. |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-07-26 : 10:45:13
|
| What are the datatypes of @EntryID and @Userpick? Remember that you are building a string so if @UserPick or @EntryID are int types, then you will need to cast them to varchar regardless of what the datatype in the table is. Secondly, Page47 suggestion of the space before WHERE was a guess (hard to tell sometimes in a post) and the @sql variable is another guess, mutually exclusive of each other. You addressed the space before WHERE, but not the @sql variable. A lot of times the @sql variable is not enough characters to hold the entire SQL statement. This should be evident if you comment out the Exec statement, add a PRINT @SQL and execute the code in Query Analyzer. This should show the the SQL that the Exec statement is trying to execute.JeremyEdited by - joldham on 07/26/2002 10:46:14 |
|
|
|
writhe
Starting Member
15 Posts |
Posted - 2002-07-26 : 11:20:28
|
| Jeremy-Sorry, I haven't done much of building these query strings. I hadn't thought of changing all the data types of the variables. Now, I've got this:SET @sql = 'UPDATE picks SET ' + @Week + ' = ' + cast(@UserPick as varchar(20)) + ' WHERE picks.EntryID = ' + cast(@EntryID as varchar(20))@sql is long enough -- I've printed it out. I think it will now work but I'm getting this error:Could not find stored procedure 'UPDATE picks SET Week1 = 5 WHERE EntryID = 1'.Do I need to change the CREATE PROCEDURE statement at the beginning of the stored procedure? |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-07-26 : 11:27:34
|
| Change EXEC @sql to EXEC sp_executesql @sql.Jeremy |
|
|
|
writhe
Starting Member
15 Posts |
Posted - 2002-07-26 : 14:10:11
|
| Jeremy-Sorry to bother you again. Hopefully, this is the last time.After making your edit at the end of the stored procedure and running it in Query Analyzer, I'm getting another data type conversion problem. Here's the error:Server: Msg 8114, Level 16, State 1, Procedure sp_UpdatePick, Line 0Error converting data type varchar to int.And here's the part of the stored procedure where the query is being written:SET @sql = 'UPDATE picks SET ' + @Week + ' = ' + cast(@UserPick as varchar(10)) + ' WHERE EntryID = ' + cast(@EntryID as varchar(10))EntryID and Week1 are both integer columns. If the variables have to be converted to create the SQL string, how are you supposed to get them into the tables? |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-07-26 : 14:17:19
|
| Use the Print command again to print the SQL statement and execute the procedure in Query Analyzer. Next, Copy the printed update statement and run it directly in Query Analayzer (instead of using the stored procedure). What is the result? Did it error out again? If so, you have a problem with the actual SQL statement you have composed.If you could show me your DDL, then could test it on my Server.Jeremy |
|
|
|
writhe
Starting Member
15 Posts |
Posted - 2002-07-26 : 14:44:42
|
| Printed, copied and ran the update in Query Analyzer successfully. If I change the print to execute, though, it chokes on data type conversion. |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-07-26 : 14:50:56
|
| Try EXEC(@SQL).I am running out of ideas. If you can post your DDL and some sample data, I will try to debug on my server.Jeremy |
|
|
|
writhe
Starting Member
15 Posts |
Posted - 2002-07-26 : 15:18:24
|
| Oh, lord. This is down right sad. All I needed were the parenthesis! Crikey!Thank you very much for your time and effort. |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-07-26 : 15:26:52
|
I'll be honest and rat on myself here. I originally thought of the () idea after we fixed the datatype problem. I couldn't remember the exact syntax to execute dynamic SQL in a stored procedure. Had an idea it was parenthesis and went into SQL Analyzer and typed in EXEC(Select * from Table). When I ran this, I received an error at SELECT. I assumed I had the wrong syntax and went to BOL for help. This is where I came up with sp_executesql. Unfortunately, I have a habit of looking for just what I want (SQL) and didn't fully read the BOL entry.  Now that I go back and check my EXEC(Select* from Table), I realize I left out the '' around the SQL EXEC('Select* from Table') seeing as how EXEC() expects a string. Writhe sorry for my dumbness. Jeremy  |
|
|
|
|
Need Dynamic SQL Help