Dynamic SQL HowTo - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Dynamic SQL HowTo

Author

Topic

Mr.k
Starting Member

2 Posts

Posted - 2014-07-07 : 11:55:52

I am trying to create a stored procedure or function that will return a list of values. I have the following (see below) and the code works and returns values when used outside of a stored procedure. As soon as I insert it into stored procedure it works but the procedure returns no values.

USE [Lawbase12]
GO
/****** Object: StoredProcedure [dbo].[LBAlerts] Script Date: 07/07/2014 11:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[LBAlerts]
@AlertLink varchar,
@serialz int output
AS
BEGIN

DECLARE @Mytable NVARCHAR(30);
DECLARE @MyField NVARCHAR(30);
DECLARE @SpecialTrigger NVARCHAR(20);
DECLARE @TriggerValue NVARCHAR(30);
DECLARE @SQL NVARCHAR(MAX)

DECLARE @output TABLE (output int);

SELECT @Mytable = TabName, @MyField = FieldName, @SpecialTrigger = SpecialTrigger, @TriggerValue = isnull(TriggerValue, '')
from Settings1 where linkserial = @AlertLink and Auto = 'Y';

/* select @MyTable, fieldname, triggervalue, specialtrigger from settings1 where linkserial = @AlertLink */

IF (@SpecialTrigger = 'Mach')
BEGIN
SET @SQL = N'SELECT PAGE0.SERIAL FROM PAGE0 LEFT OUTER JOIN ' +@MyTable+ ' ON PAGE0.SERIAL = '+ @Mytable +'.CASESERIAL
WHERE '+@MyField+' = ''' + @TriggerValue + ''' and PAGE0.STATUS = ''OPEN'' and PAGE0.serial not in (select distinct caseserial from alerts where alerts.AlertLink = '''+@AlertLink+''')'
END
ELSE IF (@SpecialTrigger = 'Empty')
BEGIN
SET @SQL = N'SELECT PAGE0.SERIAL FROM PAGE0 LEFT OUTER JOIN ' +@MyTable+ ' ON PAGE0.SERIAL = '+ @Mytable +'.CASESERIAL
WHERE ISNULL('+@MyField+', '') is null and PAGE0.STATUS = ''OPEN'' and PAGE0.serial not in (select distinct caseserial from alerts where alerts.AlertLink = '''+@AlertLink+''')'
END
ELSE IF (@SpecialTrigger = 'Not Empty')
BEGIN
SET @SQL = N'SELECT PAGE0.SERIAL FROM PAGE0 LEFT OUTER JOIN ' +@MyTable+ ' ON PAGE0.SERIAL = '+ @Mytable +'.CASESERIAL
WHERE ISNULL('+@MyField+', '') is not null and PAGE0.STATUS = ''OPEN'' and PAGE0.serial not in (select distinct caseserial from alerts where alerts.AlertLink = '''+@AlertLink+''')'
END
/*select @ExecQ */
/*INSERT @output exec sp_executesql @SQL*/
/*
select * from @output
*/
exec sp_executesql @SQL
END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-07 : 13:27:55

It's hard to see anything glaringly wrong, but then I don't have your data to work on. If you can post some sample data for the failure case (no results) as CREATE TABLE + INSERT INTO statements, it would be easier to work through it.

Also, one thing I do with procs is put test data in the proc itself. In your case, you have two parameters, so just before they are used, I'd insert something like:
[code]
-- declare @AlertLink varchar, @serialz int output; set @AlertLink = 'mytest', @serialz = 42

then I can highlight the code from the start of the declare to the end of the query and run it in ssms.

Looking at this again, I noticed that you have no length on your @AlertLink variable. that could be a source of problems.

Mr.k
Starting Member

2 Posts

Posted - 2014-07-07 : 14:33:51

You have solved my problem. As soon I added size to the @AlertLink I got my beloved values. Thanks a billion!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources