0
votes
2answers
38 views

How to use same stored procedure for different tables and avoid using dynamic sql

I have quite a few tables which contain some common columns (same name and datatype). On these columns I need to perform the same operations and I would like to do that via a stored procedure. However ...
0
votes
1answer
349 views

Using dynamic column names in PostgreSQL

I am using PostgreSSQL 8.3. I want to execute this query: select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc'; Twist in the story is column name colum_fr should be generated ...
1
vote
0answers
145 views

Prepared statement execution with variable number of parameters to be bound

Some stored procedures I work with need to interpolate WHERE criteria based on if procedure input parameters have been supplied. To avoid potential injection points, I'd like to utilize parameter ...
1
vote
1answer
155 views

How to improve performance of SP with many optional filtering parameters?

I need to write many SP that have many optional filtering parameters. After reading Turgay Sahtiyan's (Senior SQL Server Premier Field Engineer at Microsoft) blog he mentions exactly the same problem ...
1
vote
1answer
3k views

Dynamic SQL is not allowed error for creating function in MySQL

According to this question and answer of Angelo I create a MySQL function as below: DELIMITER $$ CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT, reqdTable VARCHAR(50)) RETURNS INT ...
6
votes
2answers
909 views

Why you want to avoid Dynamic SQL in stored procedure?

I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's ...
10
votes
1answer
8k views

Stored Procedure to return dynamically created table data

Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
2
votes
2answers
17k views

How to include a datetime parameter within a stored procedure along with string query?

My stored procedure is as follows, -- Add the parameters for the stored procedure here @FromDate datetime, @ToDate datetime --Select query DECLARE @query nvarchar(max) ...
3
votes
2answers
360 views

Does adherence to the DRY principle justify dynamic SQL?

Suppose I have two queries that will be frequently run against my database: SELECT UserID, UserName, UserGender FROM Users WHERE UserID = @User SELECT UserID, UserName, UserGender FROM ...