Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to pass enumerated set as a variable

Answered How to pass enumerated set as a variable

  • Wednesday, January 02, 2013 10:42 PM
     
      Has Code
    Sign In to Vote
    0

    Hi folks

    I have sql query that uses IN operator and I want the user to specify the enuration set at runtime.

    I wnat to know whether it possible to use varialbe to pass enumeration set to the query.

    Below is the sql I used to achieved this but it does no seem to work.

    CREATE PROCEDURE dbo.attrpt_all
@date1 DATE, @date2 DATE, @attc nvarchar(5), @rol nvarchar(5), @sel_attcode varchar
AS
BEGIN
  /* Procedure body */

select attevent,  attcode, date, titheno from AttRec where 
and date between @date1 and @date2  and AttRec.AttCode IN
(@sel_attcode)

 END

    Thanks in advance for your usual assistance

     

    PA

     

All Replies

  • Wednesday, January 02, 2013 10:47 PM
     
      Has Code
    Sign In to Vote
    1

    Hi paulaziz

    Here is one way:

    declare @sel_attcode varchar(max) = '1,2,3,4'
declare @sql varchar(max) = ('select attevent,  attcode, date, titheno from AttRec where 
and date between @date1 and @date2  and AttRec.AttCode IN
('+@sel_attcode+')')
execute (@sql)

    Pérez

     
  • Wednesday, January 02, 2013 10:51 PM
     
      Has Code
    Sign In to Vote
    0

    Hi paulaziz

    This is another but I was lazy with it. Post some fake table structure and data and I'll take another wack at it.

    select attevent,  attcode, date, titheno from AttRec
outer apply (
select * from (
Select 1 as attcode
union all Select 2
union all Select 3
union all Select 4
union all Select 5) ListInATable
) oa
where date between @date1 and @date2 and AttRec.AttCode IN
(oa.attcode)

    Pérez

     
  • Wednesday, January 02, 2013 11:22 PM
     
     Proposed Answer
    Sign In to Vote
    0

    I have several articles on my web site that discuss this situation and presents more method that you ever like to see:
    http://www.sommarskog.se/arrays-in-sql.html

    The articles are long, but if you have a client and SQL 2008, use table-valued parameter. Else just read the first two pages or so of the article for SQL 2005.

    But don't use dynamic SQL as suggested by Pérez - that's very bad advice!

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Wednesday, January 02, 2013 11:40 PM
     
      Has Code
    Sign In to Vote
    0

    Thanks for reply Perez.

    When I run the query it reports of error. The error message is shown below

    Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

    This is the modified execute stm:

    execute sp_ExecuteSQL @sql, N'@date1 Date', N'@date2 Date', N'@sel_attcode varchar(max)', @date1, @date2, @sel_attcode

    PA

     
  • Thursday, January 03, 2013 12:36 AM
     
      Has Code
    Sign In to Vote
    0

    Hi Paul,

    I think you might want to use a table valued parameter. Something like:

    CREATE TYPE sel_attcode AS TABLE
(sel_attcode INT); 
GO

CREATE PROCEDURE dbo.attrpt_all
@date1 DATE, @date2 DATE, @attc nvarchar(5), @rol nvarchar(5), @sel_attcode sel_attcode READONLY
AS
BEGIN
  /* Procedure body */

select attevent,  attcode, date, titheno from AttRec where 
and date between @date1 and @date2  and AttRec.AttCode IN
(SELECT sel_attcode FROM @sel_attcode)

 END
 
 DECLARE @sel_attcodeTVP AS sel_attcode;
 
 INSERT INTO @sel_attcodeTVP (sel_attcode) VALUES (1,2,3,4);
 
 EXEC dbo.attrpt_all 
 '1/1/2001', '1/1/2002', '', '', @sel_attcodeTVP

    If you're happy and you know it vote and mark.

     
  • Thursday, January 03, 2013 12:37 AM
     
     
    Sign In to Vote
    0

    Thank you Erland for advice.

    I am very grateful

    PA

     
  • Thursday, January 03, 2013 8:39 AM
     
     Answered
    Sign In to Vote
    1

    When I run the query it reports of error. The error message is shown below

    Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

    So part of trade of being a programmer is to read error message. Sometimes they are actually very clear and all you need is to comply. But as I pointed out, Pérez suggestion is not a good one. Not even if you are already using dynamic SQL.

    execute sp_ExecuteSQL @sql, N'@date1 Date', N'@date2 Date', N'@sel_attcode varchar(max)', @date1, @date2, @sel_attcode

    You have the parameter list wrong here. I have an article on my web site about dynamic SQL, which I suggest that you read,
    http://www.sommarskog.se/dynamic_sql.html
    Dynamic SQL is a fairly advanced feature, and is not really apt for new SQL programmers. As I recall, you're attempting to run a dynamic pivot. In what environment are you using this result?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked As Answer by Paul Aziz Friday, January 04, 2013 5:03 PM
    •  
     
  • Friday, January 04, 2013 4:57 PM
     
     
    Sign In to Vote
    0

    Thank Erland,

    Your article is very insightful and illuminating.  It is worth reading. I am impressed by your pedagogue approach. It is precise and full of illustrations.

    I enjoyed reading it and I wish to recommend it to all beginning SQL programmers like me

    Thanks once again Erland. I am most grateful

    PA