HELP NEED FIX MY CODE

Question

Post reply

HELP NEED FIX MY CODE

midan1

SSCertifiable

Points: 5100
More actions
October 1, 2003 at 1:52 pm

#82611

help need fix my code
on this line
WHERE mhlkot.mhlka_id IN ('+ @mhkx +')
---------------------------------------------
DECLARE @yeara [varchar](4)
DECLARE @month1 [varchar](2)
DECLARE @day1 [varchar](2)
DECLARE @mydate1 [datetime]
DECLARE @HOFSH [varchar](2)
DECLARE @nan [varchar](10)
DECLARE @mhkx VARCHAR(10)
DECLARE @mhkxa VARCHAR(50)
SET @mhkx = '1,2'
SET @yeara='2003'
SET @month1='10'
SET @nan=''
SET @HOFSH ='7'
Set @day1 ='1'
SET @mhkxa = 'WHERE mhlkot.mhlka_id IN ('+ @mhkx +')'
set @mydate1 = CONVERT([datetime],@day1 + '/'+ @month1 + '/' + @yearA,103)
SELECT
[yom1] =
CASE
WHEN (id IN
(SELECT id
FROM sn
WHERE (fld2 = 7) AND (fld1 >= @mydate1) AND (fld4 <= @mydate1) OR
(fld2 = 7) AND (fld1 <= @mydate1) AND (fld4 >= @mydate1) OR
(fld2 = 7) AND (fld1 >= @mydate1) AND (fld1 <= @mydate1)))
THEN @HOFSH
ELSE @nan
END ,
---EXEC sp_executesql @sqlCmd
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
+ @mhkxa
ORDER BY mhlkot.mhlka_id,fname
------------------------------------
thnks ilan

Viewing 3 posts - 1 through 2 (of 2 total)

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

gljjr SSChampion Points: 10080 More actions · Answer 1

Kind of hard to debug this without the table structure but I think what you need to do is change the

SET @mhkxa = 'WHERE mhlkot.mhlka_id IN ('+ @mhkx +')'

to be

SET @mhkxa = ' AND mhlkot.mhlka_id IN ('+ @mhkx +')'

You also need to turn the "SELECT [yom..." statement into a variable string and then execute it using EXEC sp_executesql. Another thing is I don't know the type of the mhlkot.mhlka_id field. You may have to change the SET @mhkx line to have single quotes around each value.

Finally there is another option and that is to create a temp table to hold the values you want in your in clause. Then simply join to that table.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Edited by - gljjr on 10/01/2003 3:47:30 PM

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.

mallier SSC Veteran Points: 214 More actions · Answer 2

--I have modified ur Query,try this,

DECLARE @yeara [varchar](4)

DECLARE @month1 [varchar](2)

DECLARE @day1 [varchar](2)

DECLARE @mydate1 [datetime]

DECLARE @HOFSH [varchar](2)

DECLARE @nan [varchar](10)

DECLARE @mhkx VARCHAR(10)

DECLARE @mhkxa VARCHAR(50)

DECLARE @sqlCmd VARCHAR(1000)

SET @mhkx = '1,2'

SET @yeara='2003'

SET @month1='10'

SET @nan=''

SET @HOFSH ='7'

Set @day1 ='1'

SET @mhkxa = 'WHERE mhlkot.mhlka_id IN ('+ @mhkx +')'

set @mydate1 = CONVERT([datetime],@day1 + '/'+ @month1 + '/' + @yearA,103)

set @sqlCmd=

'SELECT

[yom1] =

CASE

WHEN (id IN (SELECT id FROM sn WHERE (fld2 = 7) AND (fld1 >='+ @mydate1 +' ) AND (fld4 <=' + @mydate1+') OR

(fld2 = 7) AND (fld1 <='+ @mydate1 + ') AND (fld4 >='+ @mydate1 +') OR

(fld2 = 7) AND (fld1 >='+ @mydate1 + ') AND (fld1 <='+ @mydate1 +')))

THEN ' + @HOFSH + ' ELSE ' + @nan + '

END , 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 ' + @mhkxa + ' ORDER BY mhlkot.mhlka_id,fname '

EXEC sp_executesql @sqlCmd