Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
128 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
128 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  
 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.11 seconds. Powered By: Snitz Forums 2000