SQL Server Performance Forum – Threads Archive
SPROC’s on crack!
I’m writing a sproc to look up a record find its matching claim and remove it. So first part looks up location of record from a global log set at import time. Once i find location i go to server and insert it into a removed table then delete the orginal instance. Now i’m using building two sql string then exec them to do the work. but the second one doesnt act in the same manor. for debugging purposes i use prints… now when I remove the var @returncode from the print it will print the ‘nabpno 2:’ .. when i add the + @returncode it eliminates the whole line all together…IF EXISTS (SELECT name FROM sysobjects
WHERE name = ‘SP_ReversalProcess’ AND type = ‘P’)
DROP PROCEDURE SP_ReversalProcess
GO
Create Procedure SP_ReversalProcess — Variable being passed into SProc from calling program
@Payor varchar(50) = ‘AET’,
@PharmacyTranStat varchar(5) = ‘P’,
@Nabpno varchar(25) = ‘0100052’,
@Script_Id varchar(25) = ‘1098602’,
@Servicedate varchar(10) = ’12/6/2004′,
@TableName varchar(30) = ‘Aet’,
@DB varchar(30) = ‘aet2’,
@Importstamp varchar (50) = ‘AET_2006-12-12’, — OUTPUT variables
@ReturnCode varchar(50) OUTPUT,
@ServerLocation varchar(30) OUTPUT
AS — Turn on/off for debugging
set ANSI_NULLS ON
set ANSI_WARNINGS ON –*****************************************************************************
— Finding Server Location, from matching up Importstamp to the Payor_Global_log
–***************************************************************************** — Setting up local variables
DECLARE
@ImportstampString varchar (50),
@SQLStatement nvarchar(500) — Concatenating ‘%’ to end of string to be used in Where clause to search for
— server location
Set @ImportstampString = @Importstamp + char(37)
— Concatenating the SQL Command to find the Server Location
Set @SQLStatement = ‘Select @ServerLocation = Server from ‘ + @payor + ‘_global_log’
+ ‘ where process_desc like ‘
+ char(39) + @ImportstampString + char(39) — Capturing the Server Location
Exec SP_ExecuteSQL @SQLStatement, N’@ServerLocation varchar(100) output’, @ServerLocation OUTPUT
— Debugging Tool
Print ‘Sql String1: ‘ + @SQLStatement
— Debugging Tool
Print ‘Server Location is1: ‘ + @ServerLocation –******************************************************************************
— Looking for Matching Records
–****************************************************************************** SET @SQLStatement = ”
SET @SQLStatement = ‘Select @ReturnCode = patientlastname from ‘
+ @ServerLocation + ‘.’ + @DB + ‘.dbo.’ + @Tablename + ‘_claims’ +
‘ where PharmacyTranStat = ‘
+ char(39) + @PharmacyTranStat + char(39) +
‘ and Nabpno = ‘
+ @Nabpno +
‘ and Script_Id = ‘
+ @Script_ID +
‘ and ServiceDate = ‘
+ @ServiceDate Exec SP_ExecuteSQL @SQLStatement, N’@ReturnCode varchar(100) output’, @ReturnCode OUTPUT
— Debugging Tool
Print ‘Sql String2: ‘ + @SQLStatement — with the @returncode var .. it eliminates the whole line
Print ‘NABPNO 2: ‘ + @ReturnCode
— without the @returncode var .. it PRints
Print ‘NABPNO 2: ‘
Hi ya, @returncode will be null. check that the @SQLStatement gives the result you expect, if it does then it may be that sp_executesql only allows the output parameter to work with calling stored procedures…? Cheers
Twan
Your problem is due to @returncode being null. Type this into query analyzer and you will see what happens if your variables are not initialized.
declare @TestVar varchar(200)
print ‘a’
print ‘a’ + @TestVar set @TestVar = ”
print ‘a’ + @TestVar
As far as sp_executesql, output variables work just fine. For example, this will set @ObjectCount to 1 or 0 depending… set @SQL = ‘select @ObjectCount = count(id) from [‘ + @DatabaseName + ‘].dbo.sysobjects where id = @ObjectID’
set @Parameters = N’@ObjectCount int output, @ObjectID int’
exec sp_executesql @SQL, @Parameters, @ObjectCount output, @ObjectID = @ObjectID John
]]>