Hot answers tagged

33

This code works properly because it is: Parameterized, and Not doing any Dynamic SQL In order for SQL Injection to work, you have to build a query string (which you are not doing) and not translate single apostrophes (') into escaped-apostrophes ('') (those are escaped via the input parameters). In your attempt to pass in a "compromised" value, the ...


21

You can use nested EXEC calls. The database context changed by the USE persists to the child batch. DECLARE @DB SYSNAME SET @DB = 'tempdb' DECLARE @CreateViewStatement NVARCHAR(MAX) SET @CreateViewStatement = ' USE '+ QUOTENAME(@DB) +'; EXEC('' CREATE VIEW [dbo].[MyTable] AS SELECT 1 AS [Foo] '') ...


13

As others have said this is known as a PIVOT. There are several ways in which you can transform your data from rows into columns of data. If you know the values ahead of time, then you can hard-code the values. Prior to the PIVOT function you would use an aggregate function with a CASE statement. Aggregate/CASE Version: select sitecode, sum(case when ...


13

Ok, couple of things. always use EXEC when executing stored procedures; the shorthand without EXEC only works when it is the first statement in the batch (and that will not be the case here). always use semi-colon terminators - in this case they are useful in lieu of pretty carriage returns and indentation, but they are always wise to have. always use ...


12

You are too modest - your SQL is well and concisely written given the task you are undertaking. A few pointers: t1.name <> t2.name is always true if t1.name = REPLACE(t2.name, 'DUP_', '') - you can drop the former usually you want union all. union means union all then drop duplicates. It might make no difference in this case but always using union ...


11

No. There is no flag or metadata about "UsesDynamicSQL" You have to search the definition... There are 2 ways to execute dynamic SQL sp_executesql EXEC (.. or EXECUTE (' You can search for the first in sys.sql_modules, the 2nd using LIKE WHERE REPLACE(definition, ' ', '') LIKE '%EXEC(%' OR REPLACE(definition, ' ', '') LIKE '%EXECUTE(%' OR ...


11

The PRINT statement inside the WHILE loop does execute in the order you expect, but the output is buffered before sys.sp_executesql returns. Implementation details mean the buffered output is reversed. Using RAISERROR (@cmd, 0, 1) WITH NOWAIT; instead of PRINT forces the buffer to flush after each call, giving you the results in the order you expect. IIRC ...


11

Just to get these out of the way: Technically speaking, both of these options are "dynamic" / ad hoc queries that are not parsed / validated until they are submitted. And both are susceptible to SQL Injection since they are not parameterized (though with the SQLCMD scripts, if you are passing in a variable from a CMD script then you do have an opportunity ...


8

Strict DRY doesn't really apply to databases I've seen DRY is used as justification to create views for "re-use". Then we have views joining views etc and piss poor performance. Generally, similar queries will be used in different ways. One may have an aggregate, one may not, filters will be different (as above). The similarity doesn't justify dynamic SQL ...


8

As Martin Smith said, you need to pivot the data, whether with an explicit PIVOT as referenced or something like this (SQL Fiddle): SELECT SiteCode , SUM(Case When Month='Jan' Then Amount Else 0 End) MonthJan , SUM(Case When Month='Apr' Then Amount Else 0 End) MonthApr FROM Result GROUP BY SiteCode;


8

Try the following code: CREATE TABLE #Names ( [Type] VARCHAR(50), ColNum SMALLINT, ColName VARCHAR(50), ColDataType VARCHAR(20) ) INSERT INTO #Names VALUES ('Customer', 1, 'CustomerID', 'INT'), ('Customer', 2, 'CustomerName', 'VARCHAR(50)'), ('Customer', 3, 'CustomerJoinDate', 'DATE'), ('Customer', 4, 'CustomerBirthDate', 'DATE'), ...


7

Going to be pretty tough to get consensus on what "best" means, since there is a trade-off with dynamic SQL (you gain some plan stability for each version of the query, depending on parameters, but you lose things like readability, IntelliSense, etc). I've used the dynamic SQL route in many implementations and I highly recommend it. Since you will be ...


7

The other thing you could do is create a view that unions the like tables together, and include a defined column with the table name, then just do a select against that view with the tablename in the where clause. CREATE VIEW tablesviews AS SELECT columns, 'TABLE1' as tablename FROM Table1 UNION ALL SELECT columns, 'TABLE2' as tablename FROM Table2 ... ...


7

The fundamental issue is that TSQL cannot implicitly convert datetime (or integer or floating point) into character data types. It actually goes backwards, it attempts to implicitly convert the character data to the datetime (int/floating point value) based on the data type precedence rules. This is what your error message is telling you by the way, it ...


7

I am curious to know how the column names getting concatenated. Can't really answer that. Some internal workings of SQL Server creates a result like that, sometimes. The technique has been around for a considerable amount of time. Microsoft does not support the functionality and advises not to use it. From SET @local_variable (Transact-SQL) Do ...


7

Simple with hstore If you have the additional module hstore installed (instructions in link below), there is a surprisingly simple way to replace the value(s) of individual field(s) without knowing anything about other columns: Basic example: duplicate the row with id = 2 but replace 2 with 3: INSERT INTO people SELECT (p #= hstore('id', '3')).* FROM ...


6

Try this: SELECT DISTINCT OBJECT_NAME([object_id]) FROM sys.sql_dependencies AS d WHERE referenced_major_id = OBJECT_ID('dbo.YourTable') AND is_updated = 1 AND NOT EXISTS ( SELECT 1 FROM sys.sql_dependencies AS id INNER JOIN sys.columns AS c ON id.referenced_major_id = c.[object_id] AND id.referenced_minor_id = c.column_id WHERE id.object_id ...


6

I think you meant to use sp_executesql: EXEC sp_Executesql @SQL, @Parameters, @SourceTableIN = @SourceTable, @TableNotExistsOut = @TableNotExists OUTPUT And as JonSeigel pointed out in the comment, you have mis-spelled a parameter in your statement: SET @SQL = N'USE [' + @DestDB + '] IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ...


6

Here is an alternative to the test_attribs_unpivot view provided by JackPDouglas (+1) that works in versions before 11g and does fewer full table scans: CREATE OR REPLACE VIEW test_attribs_unpivot AS SELECT ID, Name, MyRow Attr#, CAST( DECODE(MyRow,1,attr1,2,attr2,3,attr3,4,attr4,attr5) AS VARCHAR2(2000)) attr FROM TEST_ATTRIBS CROSS JOIN ...


6

This sounds prime for a front end display solution. Query 1 would pull back your data, Query 2 would pull back the column names and in code when you build what ever structure you use to display you set the headers from the second query. While a Pure SQL Method may be possible it will be dynamic SQL and code maintnence would be a nightmare. Also your ...


6

The only reason why I might do that is if I needed to address an object that might not exist at compile time -- for example if I had code to create new external tables as required. As this implies, the dynamic SQL statement is not parsed when the PL/SQL compiles, so you have no idea whether it is correct or not, and dependencies are not stored in the ...


5

I'm not sure that dynamic SQL is the only answer here, though sometimes it can be the best solution if your search parameters get relatively complex. In the simple case you propose, why not a simple query that accepts one or both parameters... WHERE UserID = COALESCE(@User, UserID) AND UserName LIKE COALESCE(@Name+'%', UserName) But you will have to ...


5

You can do something along the following lines: test=> CREATE OR REPLACE FUNCTION temptabl(cnt integer) RETURNS SETOF integer AS $body$ BEGIN CREATE TEMPORARY TABLE tmp_container ON COMMIT DROP AS SELECT a FROM generate_series(1, cnt) t(a); IF (SELECT count(1) FROM tmp_container) > 5 THEN RETURN QUERY SELECT a FROM ...


5

If you just need results from multiple schemas, you can re-use the same query string and set the search_path in between: SET search_path = u111, public; SELECT * FROM foo; SET search_path = u222, public; SELECT * FROM foo; ... If you need to combine results from multiple schemas (probably your use-case), you can either build the statement in your client ...


5

Given the new information laid out in the comments, there are at least three solutions to this that don't require this mess of dynamic SQL and manual, after the fact reaction: use a ruler Seriously, he's a DBA with job responsibilities at your company. Surely you can implement a policy that tables have clustered indexes except in certain scenarios (and in ...


5

Dynamic SQL is a tool. And as a tool have some applications - for administrative works is a blessing, for example. Not so good on SP used by applications, specially if you didn't manage the parametrization of the generated code(latest versions of SQL Server reduced the problems, but still valid). I won't enter in detail here, so I'll recommend an ...


5

There is nothing wrong with using dynamic SQL if you must. In fact in some circumstances it is the only option that you have. It is more of a recommendation not to use it as yes it can lead to a SQL injection if your input is not sanitized, and yes using dynamic SQL in modules that get called often can be detrimental to it's performance. I don't think ...


5

Because RECEIVE is basically a DELETE and as such has a query plan, it must obey the same restrictions SELECT/INSERT/DELETE/UPDATE statements have, specifically the restrictions that the object it acts on must be known at compile time, not at execution time. The only option is to use dynamic SQL, with all the blessings and pitfalls that follow. You could ...


5

That's tricky, because identifiers cannot be variables in plain SQL. You need to use dynamic SQL with EXECUTE - which is still tricky, because variables are not visible inside EXECUTE. Here is a demo how to get around this: CREATE TYPE mytype AS (id int, txt text); DO $body$ DECLARE _val mytype := (1, NULL)::mytype; _name text := 'txt'; ...


5

Another method would be to use COLLATE DATABASE_DEFAULT vs specifying the collation explicitly. DECLARE @sql NVARCHAR(MAX) DECLARE @ParamDefinition NVARCHAR(MAX) DECLARE @log NVARCHAR(MAX) ,@vCrlf CHAR(2); SELECT @log = '' ,@vCrlf = CHAR(13)+CHAR(10); SELECT @sql = ' --========================================== -- ...



Only top voted, non community-wiki answers of a minimum length are eligible