using varchar variable to query a table

Question

Post reply

using varchar variable to query a table

pramod.chauhan

SSC Journeyman

Points: 91
More actions
November 5, 2008 at 6:38 am

#195661

Hello All,
This query doesnot show any result.
declare @ab varchar (100)
set @ab='''483503'''
print @ab
select * from tm_temp_empmastall where employeeid=@ab

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

nick-924707 Grasshopper Points: 19 More actions · Answer 1

declare @ab varchar (100)

set @ab='''483503'''

print @ab

select * from tm_temp_empmastall where employeeid=@ab

firstly set @ab='''483503''' is creating a variable who value is '483503'

when you probably wnat the value to be 483503

Assumin then that emoloyeeid is a varchar(100) it will work

pramod.chauhan SSC Journeyman Points: 91 More actions · Answer 2

You are right .

But query mentioned below works.

select * from tm_temp_empmastall where employeeid='483503'

steveb. SSC-Forever Points: 46733 More actions · Answer 3

It looks like you are missing out a step in your query, you are inserting a value into a variable then selecting from a table. Where is the step to insert this value into the table?

pramod.chauhan SSC Journeyman Points: 91 More actions · Answer 4

This query works.

select * from tm_temp_empmastall where employeeid='483503'

and

firstly set @ab='''483503''' is creating a variable who value is '483503'

so why

select * from tm_temp_empmastall where employeeid=@ab

doesn't work

steveb. SSC-Forever Points: 46733 More actions · Answer 5

it doesnt work because you are putting three quotes around your variable (''') you do not need to do this, surround it in single quotes (') and since the variable is the same datatype as your column (I assume) you do not need to include any quotes in the variable value

set @ab='483503'

select * from tm_temp_empmastall where employeeid=@ab

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

pramod.chauhan (11/5/2008)
firstly set @ab='''483503''' is creating a variable who value is '483503'

Why do you want to store that extra pair of single quotes as part of the variable?

The value you want to store as a variable is 483503 as an int, or 483503 as a character type.

Try this:

DECLARE @ab VARCHAR (100)

SET @ab = '483503'

SELECT CAST(@ab AS INT)

SET @ab = '''483503'''

SELECT CAST(@ab AS INT)

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pramod.chauhan SSC Journeyman Points: 91 More actions · Answer 7

thanks for qiick reply

@ab will have the all employeeid selected in reporting service interface.

and then i want to pass the @ab to stored procedure to give employee details.

there would be multiple employeeids in @ab

@ab='483503,483504'

and then

select * from tm_temp_empmastall where employeeid in (@ab)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

pramod.chauhan (11/5/2008)
thanks for qiick reply
@ab will have the all employeeid selected in reporting service interface.
and then i want to pass the @ab to stored procedure to give employee details.
there would be multiple employeeids in @ab
@ab='483503,483504'
and then
select * from tm_temp_empmastall where employeeid in (@ab)

You will need to resolve the contents of the variable for this to work. The two commonest ways are:

1. Resolve the list into a temp table or table variable

2. Use dynamic SQL, something like this:

SET @cSQL = 'select * from tm_temp_empmastall where employeeid in (' + @ab + ')'

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pramod.chauhan SSC Journeyman Points: 91 More actions · Answer 9

When i execute the following code.

declare @ab varchar (100)

declare @cSQL varchar (200)

set @ab='''483503'',''483504'''

print @ab

SET @cSQL = 'select * from tm_temp_empmastall where employeeid in (' + @ab + ')'

print @cSQL

exec @cSQL

I get the below mentioned error.

"Could not find stored procedure 'select * from tm_temp_empmastall

where employeeid in ('483503','483504')'."

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Try this...

EXEC (@cSQL)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

david.tyler SSCrazy Points: 2206 More actions · Answer 11

Hi try the following changing the table back to your table name...

declare @ab varchar (100)

declare @cSQL nvarchar (200)

set @ab='''483503'',''483504'''

SET @cSQL = 'select * from tmpEmployee where employeeid in (' + @ab + ')'

EXEC sp_executesql @cSQL

pramod.chauhan SSC Journeyman Points: 91 More actions · Answer 12

pramod.chauhan

SSC Journeyman

Points: 91

November 6, 2008 at 9:05 am

#894974

how can i do it without using dynamic sql

david.tyler SSCrazy Points: 2206 More actions · Answer 13

I can't think of a way unless the employeeid values are written to a table prior to calling the procedure. If that is the case, you can simply add a sub query following your IN clause that selects the desired values.

SELECT

*

FROM

tmpEmployee

WHERE

employeeid IN (SELECT employeeid FROM tmpValues)

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 14

Kishore.P

SSCrazy Eights

Points: 8147

November 6, 2008 at 9:17 am

#894997

if @db is int then you get the result.