Stored procedures

Check if recordset returns result

IF EXISTS (SELECT ContractID FROM RMContracts WHERE ContractNumber = @ContractNumber)
BEGIN

END

Check if something is null

if @quoteNr is Null
set @NullQuoteNr = 0;

Get ID of inserted record

insert…
SET @ID = SCOPE_IDENTITY()

Walk over records

declare @RowCnt int
declare @MaxRows int
declare @Id bigint
declare @Tms timestamp

declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , Id bigint)
insert into @Import (Id) select _Id from tbl_ where _ = @_

select @RowCnt = 1
select @MaxRows=count(*) from @Import

while @RowCnt <= @MaxRows
begin
    select @Id = Id from @Import where rownum = @RowCnt 
    select @Tms = Tms from tbl_ where _Id = @Id
    exec fs_ @Id, @Tms
    Select @RowCnt = @RowCnt + 1
end

Creating Comma Separate Values List from Table

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+', ' , '') + cast(fieldname as varchar)
FROM tablename
WHERE condition
SELECT @listStr

or, to use in subqueries:
SELECT (', ' + field)
FROM table  (NOLOCK)
WHERE condition
FOR XML PATH('')

select q.id, 
(select (qo.name + ', ') from quoteoption qo (NOLOCK) where qo.quoteid = q.id FOR XML PATH('')) as options
from quote q
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License