You can use a derived table or temporary table for example to hold the list of CustomerId then find the non matching ones with EXCEPT. The below uses a table value constructor as a derived table (compatible with SQL Server 2008+) SELECT CustomerId FROM (VALUES(1), (79), (14), (100), (123)) V(CustomerId) EXCEPT SELECT CustomerId FROM Customers
You can create a temp table and then use INSERT INTO #MyTable EXEC procedure @param. There are some other techniques listed here.
This is effectively a Do-While loop: WHILE (1=1) BEGIN — Do stuff… IF (some_condition is true) BREAK; END But as @Joel Coehoorn noted, always try to use a set based approach first. I would only resort to a loop if I can’t think of a way to solve using set operations.
You can do something like this: UPDATE dbo.profiles SET ProfileXML.modify(‘replace value of (/ProblemProfile/GroupID/text())[1] with “0”‘) WHERE id = 23 Check out this article on SQL Server 2005 XQuery and XML-DML for more details on what you can do with the .modify keyword (insert, delete, replace etc.). Marc PS: In order to get the value, it … Read more
ApplicationId must be of type UniqueIdentifier. Your code works fine if you do: DECLARE @TTEST TABLE ( TEST UNIQUEIDENTIFIER ) DECLARE @UNIQUEX UNIQUEIDENTIFIER SET @UNIQUEX = NEWID(); INSERT INTO @TTEST (TEST) VALUES (@UNIQUEX); SELECT * FROM @TTEST Therefore I would say it is safe to assume that ApplicationId is not the correct data type.
The problem with the former is that what you’re doing is USE ‘myDB’ rather than USE myDB. you’re passing a string; but USE is looking for an explicit reference. The latter example works for me. DECLARE @sql varchar(20) SELECT @sql=”USE myDb” EXEC sp_sqlexec @Sql — Also works SELECT @sql=”USE [myDb]” EXEC sp_sqlexec @Sql
The uniqueness can be enforced with a unique constraint. Whether or not the unique index is case-sensitive is defined by the server’s (or the table’s) collation. You can get the current collation of your database with this query: SELECT DATABASEPROPERTYEX(‘AdventureWorks’, ‘Collation’) SQLCollation; and you should get something like: SQLCollation ———————————— SQL_Latin1_General_CP1_CI_AS Here, the “CI_AS” at … Read more
You can’t (unfortunately) do that. A Sql Parameter can only be a single value, so you’d have to do: WHERE buildingID IN (@buildingID1, @buildingID2, @buildingID3…) Which, of course, requires you to know how many building ids there are, or to dynamically construct the query. As a workaround*, I’ve done the following: WHERE buildingID IN (@buildingID) … Read more
You can’t use a function directly as a stored procedure parameter. You can do the following: DECLARE @now DateTime SET @now = GETDATE() DECLARE @return_value int EXEC @return_value = my_stored_procedure @MyId = 1, @MyDateField = @now SELECT ‘Return Value’ = @return_value GO
you need to declare your cursor as scroll, like this declare c scroll cursor for (select statement); then at any time for locating to the first just use the following fetch first from c;