469,330 Members | 1,329 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

Dynamic sql - how to use 'if exists' with variable tables..?

Hi all

In the SP below im (trying to) do some dynamic sql. As you can see the table
to use is set as a variable and the 'exec' method used to run the
sqlstatements.
My problem is that the 'if exists' method is not doing what i was hoping it
could do.
The @presql command returns somewhere between 0 or 50 rows (give and take) -
i just want the 'if exists' part to determine if the select statement
returns something or not since i then will have to update a current row - or
insert a new one.
Even if there is no rows returned, the 'if exists' command will return true
:-/

Any suggestions to a different way of approach...?

Thanks in advance :-)

######## Stored procedure start ########
[various @ variables]
....
declare @presql varchar(200)
select @presql = 'SELECT * FROM '+@CurrentDB+' where btsiteID='+
cast(@SiteID as varchar(6))+''

IF exists((@presql))
BEGIN
declare @UpdateSQL varchar(400)
set @UpdateSQL = 'UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate as
varchar(12))+''''
exec(@UpdateSQL)
END

ELSE

BEGIN
declare @InsertSQL varchar(2000)

select @InsertSQL = 'INSERT INTO ' + @CurrentDB + '
(btDate,
btTime)
VALUES
('''+ cast(@FileDate as varchar(12)) + ''','
+ cast(@ImportTime as varchar(6)) + ')'

EXEC(@InsertSQL)
END
######## Stored procedure end ########
Jul 20 '05 #1
2 21461
oj
If exists(@sql) does not actually execute @sql and then evaluates the
resultset. What you want to do is this:

declare @presql varchar(4000)
select @presql = '
IF EXISTS(SELECT * FROM '+@CurrentDB+' where btsiteID='+cast(@SiteID as
varchar(6))+')
BEGIN
UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate as varchar(12))+'''
END
ELSE
BEGIN
INSERT INTO ' + @CurrentDB + '(btDate, btTime) VALUES ('''+ cast(@FileDate
as varchar(12)) + ''',' + cast(@ImportTime as varchar(6)) + ')
END
'
exec sp_executesql @presql
"Bane" <ba**@noname.net> wrote in message
news:p%********************@news000.worldonline.dk ...
Hi all

In the SP below im (trying to) do some dynamic sql. As you can see the
table
to use is set as a variable and the 'exec' method used to run the
sqlstatements.
My problem is that the 'if exists' method is not doing what i was hoping
it
could do.
The @presql command returns somewhere between 0 or 50 rows (give and
take) -
i just want the 'if exists' part to determine if the select statement
returns something or not since i then will have to update a current row -
or
insert a new one.
Even if there is no rows returned, the 'if exists' command will return
true
:-/

Any suggestions to a different way of approach...?

Thanks in advance :-)

######## Stored procedure start ########
[various @ variables]
...
declare @presql varchar(200)
select @presql = 'SELECT * FROM '+@CurrentDB+' where btsiteID='+
cast(@SiteID as varchar(6))+''

IF exists((@presql))
BEGIN
declare @UpdateSQL varchar(400)
set @UpdateSQL = 'UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate as
varchar(12))+''''
exec(@UpdateSQL)
END

ELSE

BEGIN
declare @InsertSQL varchar(2000)

select @InsertSQL = 'INSERT INTO ' + @CurrentDB + '
(btDate,
btTime)
VALUES
('''+ cast(@FileDate as varchar(12)) + ''','
+ cast(@ImportTime as varchar(6)) + ')'

EXEC(@InsertSQL)
END
######## Stored procedure end ########

Jul 20 '05 #2
This looks promising :-)

Thanks a bunch!
"oj" <no**********@home.com> wrote:
If exists(@sql) does not actually execute @sql and then evaluates the
resultset. What you want to do is this:

declare @presql varchar(4000)
select @presql = '
IF EXISTS(SELECT * FROM '+@CurrentDB+' where btsiteID='+cast(@SiteID as
varchar(6))+')
BEGIN
UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate as varchar(12))+'''
END
ELSE
BEGIN
INSERT INTO ' + @CurrentDB + '(btDate, btTime) VALUES ('''+ cast(@FileDate
as varchar(12)) + ''',' + cast(@ImportTime as varchar(6)) + ')
END
'
exec sp_executesql @presql
"Bane" <ba**@noname.net> wrote in message
news:p%********************@news000.worldonline.dk ...
Hi all

In the SP below im (trying to) do some dynamic sql. As you can see the
table
to use is set as a variable and the 'exec' method used to run the
sqlstatements.
My problem is that the 'if exists' method is not doing what i was hoping
it
could do.
The @presql command returns somewhere between 0 or 50 rows (give and
take) -
i just want the 'if exists' part to determine if the select statement
returns something or not since i then will have to update a current row - or
insert a new one.
Even if there is no rows returned, the 'if exists' command will return
true
:-/

Any suggestions to a different way of approach...?

Thanks in advance :-)

######## Stored procedure start ########
[various @ variables]
...
declare @presql varchar(200)
select @presql = 'SELECT * FROM '+@CurrentDB+' where btsiteID='+
cast(@SiteID as varchar(6))+''

IF exists((@presql))
BEGIN
declare @UpdateSQL varchar(400)
set @UpdateSQL = 'UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate as varchar(12))+''''
exec(@UpdateSQL)
END

ELSE

BEGIN
declare @InsertSQL varchar(2000)

select @InsertSQL = 'INSERT INTO ' + @CurrentDB + '
(btDate,
btTime)
VALUES
('''+ cast(@FileDate as varchar(12)) + ''','
+ cast(@ImportTime as varchar(6)) + ')'

EXEC(@InsertSQL)
END
######## Stored procedure end ########


Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by znelson | last post: by
9 posts views Thread by Tom | last post: by
31 posts views Thread by NickName | last post: by
1 post views Thread by Nathan Bloomfield | last post: by
6 posts views Thread by Vmusic | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.