Prev: EXECUTE permission denied on object 'sp_send_dbmail', database
Next: Sql Record number
From: Peter Hemmingsen on 11 Oct 2009 04:28
Hi

I've the following simple stored procedure to find all members in a table
where the status is in a list of values:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Test]
(
@Status varchar(30) = null
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL varchar(600)
SET @SQL = 'SELECT * FROM Medlem WHERE [Status] IN (' + @Status+ ')'
EXEC(@SQL)
END
GO

I would like the @status parameter to be optional. That is if it is NULL
everything should be returned. But since the select statement is a text
string I can't find a way to write:
@Status IS NULL OR [Status] IN (' + @Status+ ')'

Thanks in advance.

Peter



From: Uri Dimant on 11 Oct 2009 05:24
Peter
See Tony article
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/08/05/passing-an-array-csv-to-a-stored-procedure-with-data-validation-no-loops-no-self-joins-just-replace.aspx

BTW
You can issue IF ..ELSE block to check @Status for NULLs




"Peter Hemmingsen" <peter(a)hemmingsens.dk> wrote in message
news:eUHRJzkSKHA.1908(a)TK2MSFTNGP04.phx.gbl...
> Hi
>
> I've the following simple stored procedure to find all members in a table
> where the status is in a list of values:
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> CREATE PROCEDURE [dbo].[Test]
> (
> @Status varchar(30) = null
> )
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @SQL varchar(600)
> SET @SQL = 'SELECT * FROM Medlem WHERE [Status] IN (' + @Status+ ')'
> EXEC(@SQL)
> END
> GO
>
> I would like the @status parameter to be optional. That is if it is NULL
> everything should be returned. But since the select statement is a text
> string I can't find a way to write:
> @Status IS NULL OR [Status] IN (' + @Status+ ')'
>
> Thanks in advance.
>
> Peter
>
>
>


From: Olaf Helper on 11 Oct 2009 07:21
> I would like the @status parameter to be optional. That is if it is NULL
> everything should be returned. But since the select statement is a text
> string I can't find a way to write:
> @Status IS NULL OR [Status] IN (' + @Status+ ')'

Hello Peter,

example:

BEGIN
SET NOCOUNT ON
DECLARE @SQL varchar(600)
SET @SQL = 'SELECT * FROM Medlem '
IF NOT @Status IS NULL
SET @SQL = @SQL + 'WHERE [Status] IN (' + @Status+ ')'
EXEC(@SQL)
END


Greetings,
Olaf Helper
From: Erland Sommarskog on 11 Oct 2009 07:57
Peter Hemmingsen (peter(a)hemmingsens.dk) writes:
> I've the following simple stored procedure to find all members in a table
> where the status is in a list of values:
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> CREATE PROCEDURE [dbo].[Test]
> (
> @Status varchar(30) = null
> )
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @SQL varchar(600)
> SET @SQL = 'SELECT * FROM Medlem WHERE [Status] IN (' + @Status+ ')'
> EXEC(@SQL)
> END
> GO
>
> I would like the @status parameter to be optional. That is if it is NULL
> everything should be returned. But since the select statement is a text
> string I can't find a way to write:
> @Status IS NULL OR [Status] IN (' + @Status+ ')'

You are on the wrong track entirely. You should not be using dynamic
SQL at all. Do it this way:

IF @Status IS NULL
SELECT * FROM Medlem
ELSE
SELECT M.*
FROM Medlem
WHERE Status IN (SELECT str FROM list_to_table(@Status))

Where list_to_table is one the functions you find in my article on
http://www.sommarskog.se/arrays-in-sql.html

(But if you are on SQL 2008, you should use a table-valued parameter,
nothing else.)

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Peter Hemmingsen on 11 Oct 2009 10:24
Hi Erland,

Thanks a lot for your answer. I've started reading your article but havn't
finished yet.
Why shouldn't I use dynamic SQL for this. The list of status values I use
have a maximum of 5 numbers.

Peter

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9CA18E004E2D9Yazorman(a)127.0.0.1...
> Peter Hemmingsen (peter(a)hemmingsens.dk) writes:
>> I've the following simple stored procedure to find all members in a table
>> where the status is in a list of values:
>>
>> SET ANSI_NULLS ON
>> GO
>> SET QUOTED_IDENTIFIER ON
>> GO
>>
>> CREATE PROCEDURE [dbo].[Test]
>> (
>> @Status varchar(30) = null
>> )
>> AS
>> BEGIN
>> SET NOCOUNT ON
>> DECLARE @SQL varchar(600)
>> SET @SQL = 'SELECT * FROM Medlem WHERE [Status] IN (' + @Status+ ')'
>> EXEC(@SQL)
>> END
>> GO
>>
>> I would like the @status parameter to be optional. That is if it is NULL
>> everything should be returned. But since the select statement is a text
>> string I can't find a way to write:
>> @Status IS NULL OR [Status] IN (' + @Status+ ')'
>
> You are on the wrong track entirely. You should not be using dynamic
> SQL at all. Do it this way:
>
> IF @Status IS NULL
> SELECT * FROM Medlem
> ELSE
> SELECT M.*
> FROM Medlem
> WHERE Status IN (SELECT str FROM list_to_table(@Status))
>
> Where list_to_table is one the functions you find in my article on
> http://www.sommarskog.se/arrays-in-sql.html
>
> (But if you are on SQL 2008, you should use a table-valued parameter,
> nothing else.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


 |  Next  |  Last
Pages: 1 2
Prev: EXECUTE permission denied on object 'sp_send_dbmail', database
Next: Sql Record number