September 13, 2004 at 9:06 am
Hi all, I have two ouput parameters that i want to hold the row count total, for a series of incremented row count queries on 4 DB tables in the same DB.
My stored procedure is working is i dont have these output params and just use print on the incremented variables. However i want to pass these variables back to my calling programme, for testing i am using Query analyser in reality this will be VB.NET. At the moment i get the command completed successfully when i run this sp from Query Analyser. But when i try and print out the returned params they are empty. Could anyone help with this please. I have injcluded my SP code and calling code below.
thanks Dave
CREATE PROCEDURE sp_CountAudioFiles
(
@TotalAudioFiles int output,
@TotalRecorded int output
)
AS
Declare @CustomCountALL int
Declare @AutoCountALL int
Declare @TextBoxCountALL int
Declare @MultiCountALL int
Declare @CustomCountToRecord int
Declare @AutoCountToRecord int
Declare @TextBoxCountToRecord int
Declare @MultiCountToRecord int
Declare @Total int
Declare @TotalToRecord int
Declare @sql nvarchar(500)
Set @sql = 'select @mycount = count (ID) from CustomAudioTable'
EXEC sp_executesql @sql, N'@mycount int output', @CustomCountALL out
Set @sql = 'select @mycount = count (ID) from itemautoaudio'
EXEC sp_executesql @sql, N'@mycount int output', @AutoCountALL out
Set @sql = 'select @mycount = count (ID) from ItemTextboxTable'
EXEC sp_executesql @sql, N'@mycount int output', @TextBoxCountALL out
Set @sql = 'select @mycount = count (ID) from ItemMultipleChoiceTable'
EXEC sp_executesql @sql, N'@mycount int output', @MultiCountALL out
SET @Total = @CustomCountALL + @AutoCountALL + @TextBoxCountALL + @MultiCountALL
--print @Total
--######################################################################################
Set @sql = 'select @mycount = count (ID) from CustomAudioTable WHERE auS != 3'
EXEC sp_executesql @sql, N'@mycount int output', @CustomCountToRecord out
Set @sql = 'select @mycount = count (ID) from ItemAutoAudio WHERE auS != 3'
EXEC sp_executesql @sql, N'@mycount int output', @AutoCountToRecord out
Set @sql = 'select @mycount = count (ID) from ItemTextboxTable WHERE auS != 3'
EXEC sp_executesql @sql, N'@mycount int output', @TextBoxCountToRecord out
--Set @sql = 'select @mycount = count (ID) from ItemMultipleChoiceTable WHERE auS != 3'
--EXEC sp_executesql @sql, N'@mycount int output', @MultiCountToRecord out
SET @TotalToRecord = @CustomCountToRecord + @AutoCountToRecord + @TextBoxCountToRecord --+ @MultiCountToRecord
SET @TotalAudioFiles = @Total
SET @TotalRecorded = @TotalToRecord
--print @TotalToRecord
GO
#########################################################################################
CALLING CODE
#########################################################################################
Declare @i int
Declare @j-2 int
exec sp_CountAudioFiles @i, @j-2
This works
Declare @i int
Declare @j-2 int
exec sp_CountAudioFiles @i, @j-2
Print @i
print @j-2
This gives me a blank screen
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply