creating a dynamic sql query - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

creating a dynamic sql query

Author

Topic

svibuk
Yak Posting Veteran

62 Posts

Posted - 2014-05-06 : 00:41:50

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

129 Posts

Posted - 2014-05-06 : 01:11:29

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.......

svibuk
Yak Posting Veteran

62 Posts

Posted - 2014-05-06 : 01:27:28

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

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-06 : 01:51:47

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.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources