how can i do this -EXEC IN

Question

Post reply

how can i do this -EXEC IN

midan1

SSCertifiable

Points: 5100
More actions
September 30, 2003 at 2:40 pm

#160784

need help how can i do this ???
--------------
DECLARE @mh-2 [varchar](10)
DECLARE @mha[varchar](50 )
set @mh-2='1,2,3'
set @mha='(where mhlkot.mhlka_id IN (@mh))'
SELECT TOP 100 PERCENT dbo.SilokE.Fname, dbo.SilokE.id, dbo.mhlkot.mhlka, dbo.mhlkot.mhlka_id
FROM dbo.SilokE INNER JOIN
dbo.mhlkot ON dbo.SilokE.mhlka = dbo.mhlkot.mhlka_id
exec ('@mha')
----------------------------------------
thnks
ilan

Viewing 10 posts - 1 through 9 (of 9 total)

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

guarddata SSCertifiable Points: 6583 More actions · Answer 1

DECLARE

@sqlCmd NVARCHAR(1000),

@mh-2 VARCHAR(10),

@mha VARCHAR(50)

SET @mh-2 = '1,2,3'

SET @mha = 'WHERE mhlkot.mhlka_id IN (' + @mh-2 + ')'

SET @sqlCmd = N'SELECT TOP 100 PERCENT' +

' dbo.SolokE.Fname, dbo.SilokE.id,' +

' dbo.mhlkot.mhlka, dbo.mhlkot.mhlka_id' +

' FROM dbo.SilokE INNER JOIN dbo.mhlkot ON dbo.SilokE.mhlka = dbo.mhlkot.mhlka_id' + @mha

sp_executesql @sqlCmd

There may be typoes here...

Guarddata-

Edited by - guarddata on 09/30/2003 3:44:53 PM

midan1 SSCertifiable Points: 5100 More actions · Answer 2

thnks but i get an error

-------------

Server: Msg 170, Level 15, State 1, Line 11

Line 11: Incorrect syntax near 'sp_executesql'.

-------------------------------------

ilan

guarddata SSCertifiable Points: 6583 More actions · Answer 3

Assuming you are in Sql2000, you will need the line to read

EXEC sp_executesql @sqlCmd. I always forget EXEC in front

If you are in Sql 7.0, you may need EXECUTE( @sqlCmd)

Guarddata-

midan1 SSCertifiable Points: 5100 More actions · Answer 4

still an error

-----------------

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'mhlkot'.

------------------------------------------

thnks

ilan

midan1 SSCertifiable Points: 5100 More actions · Answer 5

Pardon me

it is ok the code

---------------------

thnks

ilan

guarddata SSCertifiable Points: 6583 More actions · Answer 6

Looks like the first column was names Solok instead of Silok...

Just for curiosity - how many rows are in SilokE and mhlkot?

Guarddata-

midan1 SSCertifiable Points: 5100 More actions · Answer 7

ok

----------

in siloke =(59 row(s) affected)

and in

mhlkot= (10 row(s) affected)

-----------

ilan

midan1 SSCertifiable Points: 5100 More actions · Answer 8

watch this it work like this !!!

-------------------------------------

DECLARE

@sqlCmd NVARCHAR(1000),

@mh-2 VARCHAR(10),

@mha VARCHAR(50)

SET @mh-2 = '1'

SET @mha = 'WHERE mhlkot.mhlka_id IN ('+ @mh-2 +')'

SET @sqlCmd = N'SELECT dbo.SilokE.Fname,dbo.SilokE.id, dbo.mhlkot.mhlka

FROM dbo.SilokE INNER JOIN

dbo.mhlkot ON dbo.SilokE.mhlka = dbo.mhlkot.mhlka_id

' + @mha

EXEC sp_executesql @sqlCmd

-------------------------------

ilan

gljjr SSChampion Points: 10080 More actions · Answer 9

How about using a temp/variable table to hold the values you want in your IN clause and doing away with the sp_executesql completely?



DECLARE @foo table(mhlka_id int)

insert into @foo values(1)

insert into @foo values(2)

insert into @foo values(3)



SELECT E.Fname, E.id, M.mhlka, M.mhlka_id

FROM dbo.SilokE E

    JOIN mhlkot M ON E.mhlka = M.mhlka_id

        JOIN @foo f ON M.mhlka_id = f.mhlka_id

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.