The problem is with implicit conversion. If you have Unicode/nChar/nVarChar values you are concatenating, then SQL Server will implicitly convert your string to nVarChar(4000), and it is unfortunately too dumb to realize it will truncate your string or even give you a Warning that data has been truncated for that matter! When concatenating long strings … Read more
On SQL Server 2008+ it is possible to use Table Valued Parameters to pass in a table variable to a dynamic SQL statement as long as you don’t need to update the values in the table itself. So from the code you posted you could use this approach for @TSku but not for @RelPro Example … Read more
I’ve modified the version above to run for all tables and support new SQL 2005 data types. It also retains the primary key names. Works only on SQL 2005 (using cross apply). select ‘create table [‘ + so.name + ‘] (‘ + o.list + ‘)’ + CASE WHEN tc.Constraint_Name IS NULL THEN ” ELSE ‘ALTER … Read more
Sounds like you’re calling sp_executesql with a VARCHAR statement, when it needs to be NVARCHAR. e.g. This will give the error because @SQL needs to be NVARCHAR DECLARE @SQL VARCHAR(100) SET @SQL = ‘SELECT TOP 1 * FROM sys.tables’ EXECUTE sp_executesql @SQL So: DECLARE @SQL NVARCHAR(100) SET @SQL = ‘SELECT TOP 1 * FROM sys.tables’ … Read more
It’s possible, but it requires using dynamic SQL. I recommend reading The curse and blessings of dynamic SQL before continuing… DECLARE @theDate varchar(60) SET @theDate=””‘2010-01-01” AND ”2010-08-31 23:59:59”’ DECLARE @SQL VARCHAR(MAX) SET @SQL = ‘SELECT AdministratorCode, SUM(Total) as theTotal, SUM(WOD.Quantity) as theQty, AVG(Total) as avgTotal, (SELECT SUM(tblWOD.Amount) FROM tblWOD JOIN tblWO on tblWOD.OrderID = tblWO.ID … Read more
Before you go there: for only few, known tables names, it’s typically simpler to avoid dynamic SQL and spell out the few code variants in separate functions or in a CASE construct. That said, what you are trying to achieve can be simplified and improved: CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer) LANGUAGE … Read more
DECLARE @sqlCommand NVARCHAR(1000) DECLARE @count INT DECLARE @city VARCHAR(75) SET @city = ‘New York’ SET @sqlCommand = ‘SELECT @cnt=COUNT(*) FROM customers WHERE City = @city’ EXECUTE sp_executesql @sqlCommand, N’@city nvarchar(75), @cnt int OUTPUT’, @city = @city, @cnt = @count OUTPUT SELECT @count
You may need to modify the query to include the owner if there’s more than one in the database. DECLARE @cmd varchar(4000) DECLARE cmds CURSOR FOR SELECT ‘drop table [‘ + Table_Name + ‘]’ FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE ‘prefix%’ OPEN cmds WHILE 1 = 1 BEGIN FETCH cmds INTO @cmd IF @@fetch_status != 0 … Read more
You can use the non-standard FROM clause. UPDATE b SET column1 = a.column1, column2 = a.column2, column3 = a.column3 FROM a WHERE a.id = b.id AND b.id = 1
Simple script (question asked) Building on @a_horse_with_no_name’s answer and improved with @Gregory’s comment: DO $do$ BEGIN IF EXISTS ( SELECT FROM pg_catalog.pg_roles WHERE rolname=”my_user”) THEN RAISE NOTICE ‘Role “my_user” already exists. Skipping.’; ELSE CREATE ROLE my_user LOGIN PASSWORD ‘my_password’; END IF; END $do$; Unlike, for instance, with CREATE TABLE there is no IF NOT EXISTS … Read more