| Author |
Topic |
|
ddasilva99
Starting Member
26 Posts |
 Posted - 2006-10-12 : 14:44:34
|
| Hi Everyone,Im trying to add a bit which will act as a boolean to determine if the account is enabled or not. My code below doesnt work- why?ERROR MESSAGE: Invalid operator for data type. Operator equals add, type equals bit.ALTER PROCEDURE d6661a03.Add Profile@tblName VARCHAR(50),@name VARCHAR(50),@email VARCHAR(50),@comments VARCHAR(50),@enabled bitASBEGINdeclare @SQL VARCHAR(50)set @SQL = 'INSERT INTO ' + @tblName + '(name, email, comments, enabled)' + ' VALUES( ' + @name + ', ' + @email + ', ' + @comments + ', ' + @enabled + ' )'exec (@SQL)END |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-10-12 : 14:53:57
|
| You need to properly type-cast your building of your @SQL variable. SQL will not implicitly convert a variable of type bit to a variable of type varchar(50). ....',' + @enabled + ')'.... is trying to add a bit {0,1} to a string and you want to concatenate a string to another.Jay White |
 |
|
|
ddasilva99
Starting Member
26 Posts |
Posted - 2006-10-12 : 14:58:34
|
| How do I go about fixing this problem? |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-10-12 : 15:06:05
|
| "You need to properly type-cast". I'm not going to do it for you, although there is a good chance some knucklehead will come along behind me and do it for you ... thus robbing you of the experience of figuring it out for yourself. Look up CAST or CONVERT in Books Online.Jay White |
|
|
|
ddasilva99
Starting Member
26 Posts |
Posted - 2006-10-12 : 16:35:18
|
| I tried casting but it didnt work. Any suggestions?@tblName VARCHAR(50),@name VARCHAR(50),@email VARCHAR(50),@comments VARCHAR(50),@enabled bitASBEGINdeclare @SQL VARCHAR(50)set @SQL = 'INSERT INTO ' + @tblName + '(name, email, comments, enabled)' + ' VALUES( ' + @name + ', ' + @email + ', ' + @comments + ', ' + cast(@enabled as varchar) + ' )'exec (@SQL) |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-10-12 : 16:40:03
|
| you need to specify a length when casting to varchar.as in: cast(@bleh as varchar(10)) SqlSpec - a fast, cheap, and comprehensive data dictionary generator for SQL Server 2000 and 2005 - http://www.elsasoft.org |
|
|
|
ddasilva99
Starting Member
26 Posts |
Posted - 2006-10-12 : 16:41:37
|
| Now i am getting- What am I doing wrong? lol---------------------------Microsoft Visual Studio---------------------------@enabled: this input parameter cannot be converted.---------------------------OK ---------------------------ALTER PROCEDURE d6661a03.AuthenticateUser @tblName VARCHAR(50), @name VARCHAR(50), @email VARCHAR(50), @comments VARCHAR(50), @enabled bit AS BEGIN declare @SQL VARCHAR(50) set @SQL = 'INSERT INTO ' + @tblName + '(name, email, comments, enabled)' + ' VALUES( ' + @name + ', ' + @email + ', ' + @comments + ', ' + cast(@enabled as varchar(50)) + ' )' exec (@SQL)END |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-12 : 17:16:29
|
| Your SQL statement is longer than 50 characters, so declare @SQL to be longer, probably at least 300 in this case. I have to also ask, do you really need to make this work on multiple tables? If not then the dynamic SQL is unnecessary, so I assume you're doing that because you will really be using the stored procedure for a number of different tables. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-12 : 17:19:49
|
| And one more thing - you're going to need to generate quotes around the character values in the query, so you'll need to do this in the SET statementset @SQL = 'INSERT INTO ' + @tblName + '(name, email, comments, enabled)' + ' VALUES( ''' + @name + ''', ''' + @email + ''', ''' + @comments + ''', ' + @enabled + ' )' |
|
|
|
ddasilva99
Starting Member
26 Posts |
Posted - 2006-10-12 : 17:21:56
|
| It worked! thank you SnSQL |
|
|
|
|
Inserting a Bit