July 6, 2005 at 3:24 pm
Why does this code not return a result set ???
USE Pubs
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'procReturnResultSetTest'
AND type = 'P')
DROP PROCEDURE dbo.procReturnResultSetTest
GO
CREATE PROCEDURE dbo.procReturnResultSetTest
AS
DECLARE @vchStr VARCHAR(500)
SET NOCOUNT ON
/*
Description: Show a list of names in authors table as a comma string
Errors: None
*/
-- Build String
SET @vchStr = '%'
SELECT @vchStr = CASE
WHEN LEN(@vchStr) > 0 AND @vchStr <> '%' THEN @vchStr+','+ UPPER(au_lname)
ELSE UPPER(au_lname)
END
FROM dbo.authors
GROUP BY au_lname
GO
***************
But when I add these lines before GO it does
-- Send Result
SELECT @vchStr AS CurrentUsers
I thought the SELECT in first code group would be enough ?
Also should there be any checking for @@Errors in a SELECT or SET (of variables) when getting data for a result for a report or data to client via ADO. ??
Please advise, thanks in advance !
July 6, 2005 at 3:45 pm
When you do
select column_name
it returns the data to you.
When you do
select @variable = column_name
it puts the data from the column into the variable, but it doesn't return data to a result set for display. As you have found, if you want to then see the variable contents you can
select @variable
which will return the contents of the variable.
As far as the error checking on 'select', I might check the variables that got set as a result of the select or check @@rowcount to see if any data was returned.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply