ssurekha2000 (5/1/2014)
have a sp with 4 parameters the values of this parameters is obtained from applicationcurrently i have
@transfrmdt date,
@transtodt date,
@cid integer,
@Type char(1)
DEClare @strqry varchar(max)
DEClare @STR varchar(max)
If (@Type<>'')
BEGIN
SET@strqry= @strqry +' and type='''+@Type+''''
END
If (@cname<>'')
BEGIN
SET@strqry= @strqry +'select name from Mas_C where cid='''+@cid+''''
END
SET @STR =' SELECT convert(varchar (10),transdt,111)as Transdt,amt
FROM Transcation WHERE 1=1
and Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111) '
+@strqry
print @STR
exec(@str)
SELECT sum(Amt) as TotalAmt
FROM Transcation WHERE Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111)
i am not getting the result
I take it that print @STR does not give you the query you are looking for. I suspect that you need to look at the order in which you are building up the string.
Initially you SET@strqry= @strqry +' and type='''+@Type+'''' then you set it to 'select name from Mas_C where cid='''+@cid+''''
From what I can see you need to swap the order of these, also it might help to set a value for @strqry then add any "where " filters later.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx