November 11, 2005 at 3:38 am
I've adopted a sproc used to return integer to return VARCHAR instead but still getting a convertion error... can you see what I am doing wrong?
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'SSDD' to a column of data type int.
---
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.uspSingleResultFromSQLVarchar Script Date: 11/11/2005 09:50:57 ******/
ALTER PROCEDURE uspSingleResultFromSQLVarchar
@strSQL NVARCHAR(4000),
@data NVARCHAR(100) OUTPUT
AS
DECLARE @sql AS NVARCHAR(4000)
SET @sql = ' DECLARE SOURCE_DATA CURSOR FOR '
EXECUTE sp_executesql @sql
OPEN SOURCE_DATA
FETCH NEXT FROM SOURCE_DATA INTO @data
CLOSE SOURCE_DATA
DEALLOCATE SOURCE_DATA
RETURN @data
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----
DECLARE @sql VARCHAR(1000)
DECLARE @TYPE_NAME NVARCHAR(100)
SET @sql = "SELECT TYPE_NAME FROM TRACEBASE_NEW.dbo.TBL_TYPE_DEF WHERE TYPE_ID=1"
EXEC uspSingleResultFromSQLVarchar @sql, @TYPE_NAME OUTPUT
PRINT @TYPE_NAME
November 11, 2005 at 4:11 am
Base on the error message, your trying to convert a nvarchar datatype value to an integer.
But on your stored procedure, there are no integer datatype defined.
On this query you should use single quote not double quote:
SET @sql = "SELECT TYPE_NAME FROM TRACEBASE_NEW.dbo.TBL_TYPE_DEF WHERE TYPE_ID=1"
Also maybe, the problem is in this query. Check the datatype of column TYPE_ID if it is nvarchar.
November 11, 2005 at 5:02 am
Try running this query if the error will not appear:
DECLARE @sql VARCHAR(1000)
DECLARE @TYPE_NAME NVARCHAR(100)
SET @sql = 'SELECT TYPE_NAME FROM TRACEBASE_NEW.dbo.TBL_TYPE_DEF WHERE TYPE_ID=''1'''
EXEC uspSingleResultFromSQLVarchar @sql, @TYPE_NAME OUTPUT
PRINT @TYPE_NAME
Note:
Use single quotes in this line:
SET @sql = 'SELECT TYPE_NAME FROM TRACEBASE_NEW.dbo.TBL_TYPE_DEF WHERE TYPE_ID=''1'''
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply