Dynamic if exists - 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 2000 Forums

SQL Server Development (2000)

Dynamic if exists

Author

Topic

bjornh
Yak Posting Veteran

87 Posts

Posted - 2002-08-29 : 11:01:51

hi,
I've got this sp, and works, but now I need to covert it into a dynamic sp.

alter procedure log2_insert
(
@siteID varchar(25),
@paginaID varchar(10),
@datum datetime,
@shortdatum smalldatetime,
@ip int,
@eersteBezoek int
)
AS

declare @klikpad varchar(400)
declare @SQL varchar(1000)

IF NOT EXISTS(SELECT 'True' FROM log2 WHERE datum = @shortdatum AND paginaID = @paginaID)
BEGIN
--This means the record isn't in there already, let's go ahead and add it
insert into log2 (datum, paginaID, bezoekers) values (@shortdatum, @paginaID, '1')
END
ELSE
BEGIN
--This means the record is in there already, so we just update it
update log2 set bezoekers = bezoekers + 1 where datum = @shortdatum AND paginaID = @paginaID
END

The main thing that needs te be changed is that the table log1 & log2 are actually called log1_(siteID) (ex. log1_39948) And the same with log2.

any idea's? thx
Bjorn

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-29 : 11:25:31

something like
assuming datum is character and paginaID is int.

declare @sql nvarchar(1000)
declare @i int
select @sql = 'select @i = case when exists (select * from ' + @logfilename
+ ' where datum = ''' + @shortdatum + ''' and paginaID = ' + convert(varchar(20),@paginaID) + ') then 1 else 0 end)'
exec @rc = sp_executesql @sql, N'@i int output', @i output
if @i = 0
begin

select @sql = 'insert into ' + @logname + ' (datum, paginaID, bezoekers) values ('
+ ''' + @shortdatum + ''', ' + convert(varchar(20),@paginaID) + ', ''1'')'

exec (@sql)

end
else
begin
...
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bjornh
Yak Posting Veteran

87 Posts

Posted - 2002-08-29 : 12:18:12

ok, I still need to get rid of some bumbs, but I think I can work it out now. Thx!

Bjorn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources