Tag Info

Hot answers tagged

23

Index names in PostgreSQL Index names are unique across a single database schema. Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema. Two tables in the same schema cannot have an index of the same name. (Follows logically.) If you do not care about the name ...


12

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] '') ...


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 ...


10

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 ...


10

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 ...


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 ...


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

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 ...


6

Ok, couple of things. always use EXEC when executing stored procedures; the shorthand without EXEC only works when it is the only statement in the batch (and that will not 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 ...


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

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

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 ...


6

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 ... ...


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

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

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

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

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

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

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'), ...


4

This particular example can be simpler. You can TRUNCATE multiple tables at once. Aggregate all tablenames and execute a single statement: CREATE OR REPLACE FUNCTION truncate_tables(_username text) RETURNS void AS $func$ BEGIN EXECUTE ( SELECT 'TRUNCATE TABLE ' || string_agg(quote_ident(t.tablename), ', ') || ' CASCADE;' ...


4

Try dispensing with the explicit cursor: begin; set role dba; create role stack; grant stack to dba; create schema authorization stack; set role stack; -- create table foo(id serial); insert into foo default values; create or replace function truncate_tables(username in varchar) returns void as $$ declare r record; begin for r in (select tablename ...


4

I'd have 2 separate queries/stored procedures just to avoid dynamic SQL. An IF statement in one stored proc would be enough too


4

In addition to what @filiprem wrote, here is how you do this properly: ... DECLARE tbl_var text := 'myTable'; -- I would not use mixed case names .. BEGIN EXECUTE ' CREATE TABLE ' || quote_ident(tbl_var) || '( foo integer NOT NULL, bar text NOT NULL)'; ... Use quote_ident() to avoid SQL injection or syntax errors. It will quote names with ...


4

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 ...


3

This is a working sample receive procedure that is re-usable over different queues. ALTER PROCEDURE [dbo].[zBroker_OnReceive_WFItemsUpdated_Queue] AS BEGIN SET NOCOUNT ON; DECLARE @h UNIQUEIDENTIFIER; DECLARE @messageType int; DECLARE @Packet VARCHAR(50); DECLARE @MyId ...


3

To elaborate on gbn's answer, two sprocs is the better way to go here; additionally, I'd go one step further and place the query (sans top clause) in a table-valued function and then have the sprocs be nothing more than a shell which calls the TVF and applies the top clause appropriately (by number of rows or percent). The only caveat here being that if you ...


3

There is an implicit CREATE TABLE at the beginning and DROP TABLE @T at the end as the table goes out of scope that aren't associated with either the INSERT or the SELECT statement. If you replace with a #temp table you should see some additional reads for both the drop and create statements. For me I see 36 for the CREATE and 100 for the DROP so that ...


3

One way to get this done is probably something you have already done, and that is to replace your line: if @DebugMode=1 print @SQL with if @DebugMode=1 print @SQL + ' ' + convert(nvarchar(max), @Foobar) And you would have to do it this way for all your variables, you will need to convert them manually to avoid conversion errors. You could also use ...



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