Home Direct query returns data but inner stored procedure query does not?
Reply: 0

Direct query returns data but inner stored procedure query does not?

user1346
1#
user1346 Published in August 15, 2018, 5:38 am

I have a stored procedure which looks like this:

CREATE PROCEDURE [dbo].[GetGroupUsers]
    @GroupName VARCHAR(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    PRINT @GroupName

    -- get group users
    DECLARE @GroupUsers TABLE
        (
            GroupId UNIQUEIDENTIFIER, 
            GroupName VARCHAR(500),
            UserId UNIQUEIDENTIFIER
        )

    INSERT INTO @GroupUsers 
        SELECT
            GroupId, GroupName, UserId
        FROM 
            AllGroupUsers agu
        WHERE 
            (@GroupName IS NULL OR agu.[GroupName] LIKE @GroupName + '%')

    SELECT * FROM @GroupUsers
END

For some reason, the stored procedure above isn't returning data when I execute it through the SSMS test dialogue with a @GroupName value of _MY_TEST although data is returned if I run the following query directly from a different query window:

select * 
from AllGroupUsers agu
where agu.[GroupName] like '_MY_TEST' + '%'

Any idea what the problem might be?

The print @GroupName command is printing the expected value and I also tried to change the @GroupName type from VARCHAR(500) to NVARCHAR(500) but still no results are returned from the stored procedure?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302217 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO