• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

declare string

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
donnatronious
Asked:
donnatronious
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
SwindleCommented:
You could just make the varchar field the maximum size and that should do it for you:

DECLARE @test varchar(max)
0
 
mastooCommented:
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
 
donnatroniousAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SwindleCommented:
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
 
donnatroniousAuthor Commented:
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
 
donnatroniousAuthor Commented:
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
 
donnatroniousAuthor Commented:
got it set @test = '(20186983,13310584)'
0
 
SwindleCommented:
set @test = '20186983,13310584'
0
 
mastooCommented:
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
 
dqmqCommented:
>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now