All Questions

44
votes
6answers
45604 views

Table name as a PostgreSQL function parameter

I want to pass a table name as a parameter in a Postgres function. I tried this code: CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer AS $$ BEGIN IF EXISTS (select...
22
votes
3answers
13158 views

Refactor a PL/pgSQL function to return the output of various SELECT queries

I wrote a function that outputs a PostgreSQL SELECT query well formed in text form. Now I don't want to output a text anymore, but actually run the generated SELECT statement against the database and ...
201
votes
16answers
154627 views

Why would someone use WHERE 1=1 AND <conditions> in a SQL clause?

Why would someone use WHERE 1=1 AND <conditions> in a SQL clause (Either SQL obtained through concatenated strings, either view definition) I've seen somewhere that this would be used to protec...
68
votes
15answers
94590 views

In SQL Server, how do I generate a CREATE TABLE statement for a given table?

I've spent a good amount of time coming up with solution to this problem, so in the spirit of this post, I'm posting it here, since I think it might be useful to others. If anyone has a better scrip...
136
votes
8answers
206658 views

How to get sp_executesql result into a variable?

I have a piece of dynamic SQL I need to execute, I then need to store the result into a variable. I know I can use sp_executesql but can't find clear examples around about how to do this.
25
votes
2answers
16130 views

INSERT with dynamic table name in trigger function

I'm not sure how to achieve something like the following: CREATE OR REPLACE FUNCTION fnJobQueueBEFORE() RETURNS trigger AS $$ DECLARE shadowname varchar := TG_TABLE_NAME || 'shadow'; ...
104
votes
8answers
87481 views

Truncating all tables in a Postgres database

I regularly need to delete all the data from my PostgreSQL database before a rebuild. How would I do this directly in SQL? At the moment I've managed to come up with a SQL statement that returns all ...
79
votes
6answers
112940 views

Getting result of dynamic SQL into a variable for sql-server

Executing dynamic SQL as follows in Stored Procedure: DECLARE @sqlCommand nvarchar(1000) DECLARE @city varchar(75) SET @city = 'London' SET @sqlCommand = 'SELECT COUNT(*) FROM customers WHERE City = ...
49
votes
8answers
139098 views

How to use table variable in a dynamic sql statement?

In my stored procedure I declared two table variables on top of my procedure. Now I am trying to use that table variable within a dynamic sql statement but I get this error at the time of execution of...
11
votes
1answers
7383 views

Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?

I am trying to execute an SQL command within dynamic SQL with bind variables: -- this procedure is a part of PL/SQL package Test_Pkg PROCEDURE Set_Nls_Calendar(calendar_ IN VARCHAR2) IS BEGIN EXEC...

Previous Next