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

[Bane]

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

[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 ########

[Bane]

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