select distinct stuff(( select ‘,’ + u.username from users u where u.username = username order by u.username for xml path(”) ),1,1,”) as userlist from users group by username had a typo before, the above works
Here is slightly modified version. Changes are noted as code commentary. BEGIN TRANSACTION declare @cnt int declare @test nvarchar(128) — variable to hold table name declare @tableName nvarchar(255) declare @cmd nvarchar(500) — local means the cursor name is private to this code — fast_forward enables some speed optimizations declare Tests cursor local fast_forward for SELECT … Read more
IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N’function_name’) AND xtype IN (N’FN’, N’IF’, N’TF’) ) DROP FUNCTION function_name GO If you want to avoid the sys* tables, you could instead do (from here in example A): IF object_id(N’function_name’, N’FN’) IS NOT NULL DROP FUNCTION function_name GO The main thing to catch is … Read more
This is similar to Adam Robinson’s, but uses ISNULL instead of COUNT. SELECT ISNULL( (SELECT 1 FROM Sites S WHERE S.Id = @SiteId and S.Status = 1 AND (S.WebUserId = @WebUserId OR S.AllowUploads = 1)), 0) If the inner query has a matching row, then 1 is returned. The outer query (with ISNULL) then returns … Read more
GO is not properly a TSQL command. Instead it’s a command to the specific client program which connects to an SQL server (Sybase or Microsoft’s – not sure about what Oracle does), signalling to the client program that the set of commands that were input into it up till the “go” need to be sent … Read more
To delete all records from a table without deleting the table. DELETE FROM table_name use with care, there is no undo! To remove a table DROP TABLE table_name
you have to call it like this SELECT dbo.CheckIfSFExists(23, default) From Technet: When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called in order to retrieve the default value. This behaviour is different from using parameters with default values in stored procedures in which … Read more
You need to put the CTE first and then combine the INSERT INTO with your select statement. Also, the “AS” keyword following the CTE’s name is not optional: WITH tab AS ( bla bla ) INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos ( BatchID, AccountNo, APartyNo, SourceRowID ) SELECT * FROM tab Please note that the code assumes that … Read more
Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use … Read more
If you have OUTPUT parameters you can do DECLARE @retval int DECLARE @sSQL nvarchar(500); DECLARE @ParmDefinition nvarchar(500); DECLARE @tablename nvarchar(50) SELECT @tablename = N’products’ SELECT @sSQL = N’SELECT @retvalOUT = MAX(ID) FROM ‘ + @tablename; SET @ParmDefinition = N’@retvalOUT int OUTPUT’; EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT; SELECT @retval; But if you don’t, and can … Read more