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
SELECT unnest(array[‘Sl.no’, ‘username’, ‘Designation’,’salary’]) AS “Columns”, unnest(array[Sl.no, username, value3Count,salary]) AS “Values” FROM view_name ORDER BY “Columns” Reference : convertingColumnsToRows
You will need to use the PL/PgSQL EXECUTE statement, via a DO block or PL/PgSQL function (CREATE OR REPLACE FUNCTION … LANGUAGE plpgsql). Dynamic SQL is not supported in the ordinary SQL dialect used by PostgreSQL, only in the procedural PL/PgSQL variant. DO $$ BEGIN EXECUTE format(‘CREATE TABLE %I AS SELECT * FROM backup’, ‘backup_’ … Read more
Modern PostgreSQL format() has a built-in way to escape identifiers. Simpler than before: CREATE OR REPLACE FUNCTION foo_before() RETURNS trigger LANGUAGE plpgsql AS $func$ BEGIN EXECUTE format(‘INSERT INTO %I.%I SELECT $1.*’ , TG_TABLE_SCHEMA, TG_TABLE_NAME || ‘shadow’) USING OLD; RETURN OLD; END $func$; Works with a VALUES expression as well. db<>fiddle here Old sqlfiddle Major points … Read more
Dynamic SQL is merely where the query has been built on the fly – with some vendors, you can build up the text of the dynamic query within one stored procedure, and then execute the generated SQL. In other cases, the term merely refers to a decision made by code on the client (this is … Read more
EXECUTE … USING only works in PL/PgSQL – ie within functions or DO blocks written in the PL/PgSQL language. It does not work in plain SQL; the EXECUTE in plain SQL is completely different, for executing prepared statements. You cannot use dynamic SQL directly in PostgreSQL’s SQL dialect. Compare: PL/PgSQL’s EXECUTE … USING; to SQL’s … Read more
Dynamic SQL and RETURN type (I saved the best for last, keep reading!) You want to execute dynamic SQL. In principal, that’s simple in plpgsql with the help of EXECUTE. You don’t need a cursor. In fact, most of the time you are better off without explicit cursors. The problem you run into: you want … Read more
Try: SELECT into #T1 execute (‘execute ‘ + @SQLString ) And this smells real bad like an sql injection vulnerability. correction (per @CarpeDiem’s comment): INSERT into #T1 execute (‘execute ‘ + @SQLString ) also, omit the ‘execute’ if the sql string is something other than a procedure
A cursor will only accept a select statement, so if the SQL really needs to be dynamic make the declare cursor part of the statement you are executing. For the below to work your server will have to be using global cursors. Declare @UserID varchar(100) declare @sqlstatement nvarchar(4000) –move declare cursor into sql to be … Read more
Basic query This query creates all necessary DDL statements: SELECT ‘DROP FUNCTION ‘ || oid::regprocedure FROM pg_proc WHERE proname=”my_function_name” — name without schema-qualification AND pg_function_is_visible(oid); — restrict to current search_path Output: DROP FUNCTION my_function_name(string text, form text, maxlen integer); DROP FUNCTION my_function_name(string text, form text); DROP FUNCTION my_function_name(string text); Execute the commands after checking plausibility. … Read more