?
Solved

declare string

Posted on 2007-08-09
10
Medium Priority
?
322 Views
Last Modified: 2010-03-19
It should be obvious what I want to happen.  Some times @test will equal a list of 50 or so numbers, they are INT.  Can I just declare as a string that gets placed rather than declaring datatype?

declare @test varchar(256)
set @test = (20186983,13310584)

SELECT A.curentrecno, A.srcrecno,  count(*) cnt_srcrecno
      FROM InitiateHub.mpi_entlink_pr AS A
      where curentrecno in @test
      GROUP BY A.curentrecno, A.srcrecno
0
Comment
Question by:donnatronious
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 11

Expert Comment

by:Swindle
ID: 19663809
You could just make the varchar field the maximum size and that should do it for you:

DECLARE @test varchar(max)
0
 
LVL 21

Accepted Solution

by:
mastoo earned 700 total points
ID: 19663811
One approach...

declare @test varchar(256)
set @test = (20186983,13310584)
Declare @sSql varchar(500)

Set @sSql = 'SELECT A.curentrecno, A.srcrecno,  count(*) cnt_srcrecno
      FROM InitiateHub.mpi_entlink_pr AS A
      where curentrecno in ' + @test + ' GROUP BY A.curentrecno, A.srcrecno'
Exec( @sSql )
0
 

Author Comment

by:donnatronious
ID: 19663889
swindle, that does not work
declare @test as varchar(max)
    set @test = (20186983,13310584)
SELECT A.curentrecno, A.srcrecno,  count(*) cnt_srcrecno
      FROM InitiateHub.mpi_entlink_pr AS A
      where curentrecno in @test
      GROUP BY A.curentrecno, A.srcrecno
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:Swindle
ID: 19663962
Yeah, I'm sorry when I read this I thought the query was working and that you just couldn't fit all 50 of your strings into that variable.  mastoo has the right answer.
0
 

Author Comment

by:donnatronious
ID: 19664075
So let me make sure I understand.  There is no way within a normal query in SQL Server Management Studio to "include" some code to be executed, similar to a server side include in HTML?

Without executing the way mastoo describes?
0
 

Author Comment

by:donnatronious
ID: 19664084
Mastoo,

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

declare @test varchar(256)
set @test = (20186983,13310584)
Declare @sSql varchar(500)

Set @sSql = 'SELECT A.curentrecno, A.srcrecno,  count(*) cnt_srcrecno
      FROM InitiateHub.mpi_entlink_pr AS A
      where curentrecno in ' + @test + ' GROUP BY A.curentrecno, A.srcrecno'
Exec( @sSql )
0
 

Author Comment

by:donnatronious
ID: 19664091
got it set @test = '(20186983,13310584)'
0
 
LVL 11

Expert Comment

by:Swindle
ID: 19664100
set @test = '20186983,13310584'
0
 
LVL 21

Expert Comment

by:mastoo
ID: 19664261
Ah yes, sorry.  I should know anything more than one line of typing and I'll have a mistake if I don't actually test it.  But yes, adding the single quotes was correct.
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 300 total points
ID: 19664264
>got it set @test = '(20186983,13310584)'

That's correct

>So let me make sure I understand.  There is no way within a normal query in SQL Server Management Studio to "include" some code to be executed, similar to a server side include in HTML?

IN operates on a set.  Unfortnately, it does not parse a comma-delimited string into a set.  Somehow you need to convert the string to a set of numbers.    

You can create a table-valued function that accepts the comma-delimited string as an argument and returns a table with one item per row

Then,

Select ...
From ....
Where curentrecno in (Select * from YourFunction(@test))

0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question