October 5, 2002 at 7:29 pm
Anyone know why I get this error and how to fix it?
Query Analyzer:
usp_InsertANswersAboutThem 'AboutThem_BodyArt','BodyArtID',77,1
------------------
Stored Procedure:
CREATE PROCEDURE usp_InsertAnswersAboutThem
@TableName varchar(100),
@ColumnName varchar(100),
@MemberID int,
@AnswerID int
AS
DECLARE @Findmember varchar (1000)
DECLARE @FindAnswer varchar (1000)
DECLARE @DoInsert varchar (1000)
DECLARE @DoUpdate varchar (1000)
SET @Findmember = 'SELECT MAX(MemberID) FROM ' + @tablename + ' Where MemberID = ' + CAST(@MemberID AS varchar(8))
SET @FindAnswer = 'SELECT MAX(' + @ColumnName + ') FROM ' + @tablename + ' Where ' + @ColumnName + ' = ' + CAST(@AnswerID AS varchar(8))
SET @DoInsert = 'INSERT INTO ' + @tablename + ' (MemberID, ' + @ColumnName + ') VALUES (' + CAST(@MemberID AS varchar(8)) + ',' + CAST(@AnswerID AS varchar(8)) + ')'
SET @DoUpdate = 'UPDATE ' + @tablename + ' SET MemberID = ' + CAST(@MemberID AS varchar(8)) + ', ' + @ColumnName + ' = ' + CAST(@AnswerID AS varchar(8)) + ' WHERE MemberID = ' + CAST(@MemberID AS varchar(8)) + ' AND ' + @ColumnName + ' = ' + CAST(@AnswerID AS varchar(8))
PRINT @Findmember
PRINT @FindAnswer
PRINT @DoInsert
PRINT @DoUpdate
IF (SELECT @Findmember) > 0
BEGIN
IF (SELECT @FindAnswer) > 0
BEGIN
EXEC (@DoUpdate)
END
END
ELSE
BEGIN
EXEC (@DoInsert)
END
GO
----------------
Output:
SELECT MAX(MemberID) FROM AboutThem_BodyArt Where MemberID = 77
SELECT MAX(BodyArtID) FROM AboutThem_BodyArt Where BodyArtID = 1
INSERT INTO AboutThem_BodyArt (MemberID, BodyArtID) VALUES (77,1)
UPDATE AboutThem_BodyArt SET MemberID = 77, BodyArtID = 1 WHERE MemberID = 77 AND BodyArtID = 1
Server: Msg 245, Level 16, State 1, Procedure usp_InsertAnswersAboutThem, Line 25
Syntax error converting the varchar value 'SELECT MAX(MemberID) FROM AboutThem_BodyArt Where MemberID = 77' to a column of data type int.
Thanks in advance,
Rich
Edited by - EdenMachine on 10/05/2002 7:33:04 PM
October 5, 2002 at 10:52 pm
It will be because of these two lines.
IF (SELECT @Findmember) > 0
IF (SELECT @FindAnswer) > 0
You will need to use sp_executesql with an output variable to get the values of max based on dynamic query. Do not have in front me but several threads have touched on this.
Otherwise you are comparing the string text to 0 and that is not allowed.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 6, 2002 at 7:11 pm
This didn't seem to work 🙁
CREATE PROCEDURE usp_InsertAnswersAboutThem
@TableName varchar(100),
@ColumnName varchar(100),
@MemberID int,
@AnswerID int
AS
DECLARE @FindMember varchar (1000)
DECLARE @FindAnswer varchar (1000)
DECLARE @DoInsert varchar (1000)
DECLARE @DoUpdate varchar (1000)
DECLARE @VarCompare1 int
DECLARE @VarCompare2 int
SET @FindMember = 'SELECT MAX(MemberID) FROM ' + @tablename + ' Where MemberID = ' + CAST(@MemberID AS varchar(8))
SET @FindAnswer = 'SELECT MAX(' + @ColumnName + ') FROM ' + @tablename + ' Where ' + @ColumnName + ' = ' + CAST(@AnswerID AS varchar(8))
SET @DoInsert = 'INSERT INTO ' + @tablename + ' (MemberID, ' + @ColumnName + ') VALUES (' + CAST(@MemberID AS varchar(8)) + ',' + CAST(@AnswerID AS varchar(8)) + ')'
SET @DoUpdate = 'UPDATE ' + @tablename + ' SET MemberID = ' + CAST(@MemberID AS varchar(8)) + ', ' + @ColumnName + ' = ' + CAST(@AnswerID AS varchar(8)) + ' WHERE MemberID = ' + CAST(@MemberID AS varchar(8)) + ' AND ' + @ColumnName + ' = ' + CAST(@AnswerID AS varchar(8))
PRINT @Findmember
PRINT @FindAnswer
PRINT @DoInsert
PRINT @DoUpdate
IF (EXECUTE sp_executesql @FindMember) > 0
BEGIN
IF (EXECUTE sp_executesql @FindAnswer) > 0
BEGIN
EXEC (@DoUpdate)
END
END
ELSE
BEGIN
EXEC (@DoInsert)
END
GO
October 7, 2002 at 3:42 am
Sorry, I now have an example based on what you are doing. Try this
CREATE PROCEDURE usp_InsertAnswersAboutThem
@TableName varchar(100),
@ColumnName varchar(100),
@MemberID int,
@AnswerID int
AS
DECLARE @FindMember nvarchar (2000)
DECLARE @FindAnswer nvarchar (2000)
DECLARE @DoInsert varchar (1000)
DECLARE @DoUpdate varchar (1000)
DECLARE @FindMemberVal int
DECLARE @FindAnserVal int
DECLARE @VarCompare1 int
DECLARE @VarCompare2 int
SET @FindMember = 'SELECT @FindMemberVal = MAX(MemberID) FROM ' + @tablename + ' Where MemberID = ' + CAST(@MemberID AS varchar(8))
SET @FindAnswer = 'SELECT @FindAnserVal = MAX(' + @ColumnName + ') FROM ' + @tablename + ' Where ' + @ColumnName + ' = ' + CAST(@AnswerID AS varchar(8))
SET @DoInsert = 'INSERT INTO ' + @tablename + ' (MemberID, ' + @ColumnName + ') VALUES (' + CAST(@MemberID AS varchar(8)) + ',' + CAST(@AnswerID AS varchar(8)) + ')'
SET @DoUpdate = 'UPDATE ' + @tablename + ' SET MemberID = ' + CAST(@MemberID AS varchar(8)) + ', ' + @ColumnName + ' = ' + CAST(@AnswerID AS varchar(8)) + ' WHERE MemberID = ' + CAST(@MemberID AS varchar(8)) + ' AND ' + @ColumnName + ' = ' + CAST(@AnswerID AS varchar(8))
--This sections will get the value returned from the dynamic SQL using sp_executesql setting it to a variable.
EXECUTE sp_executesql @FindMember, '@FindMemberVal int OUTPUT', @FindMemberVal OUTPUT
EXECUTE sp_executesql @FindAnswer, '@FindAnserVal int OUTPUT', @FindAnserVal OUTPUT
--End section
PRINT @Findmember
PRINT @FindAnswer
PRINT @FindMemberVal
PRINT @FindAnserVal
PRINT @DoInsert
PRINT @DoUpdate
IF (@FindMemberVal) > 0
BEGIN
IF (@FindAnserVal) > 0
BEGIN
EXEC (@DoUpdate)
END
END
ELSE
BEGIN
EXEC (@DoInsert)
END
GO
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 7, 2002 at 7:54 am
Hmm, now I'm getting this error about the sp_executesql line...
Server: Msg 214, Level 16, State 3, Procedure sp_executesql, Line 25
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.
October 7, 2002 at 10:50 am
Ooops sorry about that, change
EXECUTE sp_executesql @FindMember, '@FindMemberVal int OUTPUT', @FindMemberVal OUTPUT
EXECUTE sp_executesql @FindAnswer, '@FindAnserVal int OUTPUT', @FindAnserVal OUTPUT
to
EXECUTE sp_executesql @FindMember, N'@FindMemberVal int OUTPUT', @FindMemberVal OUTPUT
EXECUTE sp_executesql @FindAnswer, N'@FindAnserVal int OUTPUT', @FindAnserVal OUTPUT
and should correct.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 7, 2002 at 10:57 am
That was the key!!! What exactly does the "N" do anyway so i can LEARN to fish instead of ASKING for fish? 🙂
Thanks,
Rich
October 7, 2002 at 12:21 pm
N is an explicit hint to cast the string data to NVARCHAR when defining a quoted string value.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply