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