SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 creating a dynamic sql query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

svibuk
Yak Posting Veteran

62 Posts

Posted - 05/06/2014 :  00:41:50  Show Profile  Reply with Quote
have a sp with 4 parameters the values of this parameters is obtained from application

currently i have







i am not getting the result

@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)




the out needed is display
1) date,amt with the given date range
2) conditional where clause ie if @type<>'' then
select * from tbl where date between @frmdt and @todt and type=@type
if @cid<>'' then
select * from tbl where date between @frmdt and @todt and cid=@cid

if both not blank

select * from tbl where date between @frmdt and @todt and cid=@cid and type=@type

and both blank then
select * from tbl where date between @frmdt and @todt

MuralikrishnaVeera
Posting Yak Master

India
121 Posts

Posted - 05/06/2014 :  01:11:29  Show Profile  Reply with Quote
quote:
Originally posted by svibuk

have a sp with 4 parameters the values of this parameters is obtained from application

currently i have







i am not getting the result

@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)




the out needed is display
1) date,amt with the given date range
2) conditional where clause ie if @type<>'' then
select * from tbl where date between @frmdt and @todt and type=@type
if @cid<>'' then
select * from tbl where date between @frmdt and @todt and cid=@cid

if both not blank

select * from tbl where date between @frmdt and @todt and cid=@cid and type=@type

and both blank then
select * from tbl where date between @frmdt and @todt



I faced same kind of situation earlier
What you need to do is
1 Mistake --SET @strqry= ' and type='''+@Type+'''
2 Mistake --SET @strqry= 'select name from Mas_C where cid='+CAST(@cid AS VARCHAR(MAX))


---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page

svibuk
Yak Posting Veteran

62 Posts

Posted - 05/06/2014 :  01:27:28  Show Profile  Reply with Quote
quote:
Originally posted by MuralikrishnaVeera

quote:
Originally posted by svibuk

have a sp with 4 parameters the values of this parameters is obtained from application

currently i have







i am not getting the result

@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)




the out needed is display
1) date,amt with the given date range
2) conditional where clause ie if @type<>'' then
select * from tbl where date between @frmdt and @todt and type=@type
if @cid<>'' then
select * from tbl where date between @frmdt and @todt and cid=@cid

if both not blank

select * from tbl where date between @frmdt and @todt and cid=@cid and type=@type

and both blank then
select * from tbl where date between @frmdt and @todt



I faced same kind of situation earlier
What you need to do is
1 Mistake --SET @strqry= ' and type='''+@Type+'''
2 Mistake --SET @strqry= 'select name from Mas_C where cid='+CAST(@cid AS VARCHAR(MAX))


---------------
Murali Krishna

You live only once ..If you do it right once is enough.......



can u explain

abobe u have cancelled the query
& below u have stated it as a mistake
then how can i join the paramaters if they are not blank
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
121 Posts

Posted - 05/06/2014 :  01:51:47  Show Profile  Reply with Quote
What i mean is replace your code with the lines i stated IN RED color

1.SET @strqry= ' and type='''+@Type+''
2 SET @strqry= 'select name from Mas_C where cid='+CAST(@cid AS VARCHAR(MAX))


You need to convert the integer value to varchar when you are using it in DYNAMIC QUERY ..
If you are placing @cid in Quotations leads to error(Variable @cid is not declared)



---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Edited by - MuralikrishnaVeera on 05/06/2014 02:19:02
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000