January 24, 2006 at 2:18 pm
Hello Everydody,
I am trying to develop Function where I am trying to pass a @WhereClause as the table name. But I am getting an error .Would be greatful if anyone can help me develop the function.
Thanks,
Kapil
CREATE FUNCTION GetSelectedDate
(
@StartDate datetime,
@EndDate datetime,
@WhereClause varchar(50)
)
Returns table
AS
RETURN
{
DECLARE @sql varchar(2000)
SET @sql = 'Select * From ' + @WhereClause + ' where InDate ='' ' + @StartDate + ''' And OutDate ='' ' + @EndDate +''''
EXEC @sql
}
January 24, 2006 at 2:33 pm
What is the error?
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
This is caused because you are using curly braces instead of () parenthesis.
Second,
You cannot use exec or any other statement that does not return a table value within a funciton
Try using a stored procedure instead of a function.
Anytime I see someone struggling with dynamic sql I like to attach this article
http://www.sommarskog.se/dynamic_sql.html
January 24, 2006 at 3:36 pm
I exec the store procedure as exec IsEffective 'LocationLink'
Could not find stored procedure 'Select InDate,AccountID,OutDate From LocationLink'.
Pls guide..
Kapil
CREATE PROCEDURE IsEffective
(
@WhereClause varchar(1000)
)
AS
DECLARE @sql varchar(2000)
SET @sql = ''
IF @WhereClause !=''
BEGIN
SET @sql = 'Select InDate,AccountID,OutDate From ' + @WhereClause
END
Exec @sql
GO
January 24, 2006 at 4:58 pm
Need Parens around the @sql param.
create PROCEDURE IsEffective
(
@WhereClause varchar(1000)
)
AS
DECLARE @sql nvarchar(2000)
SET @sql = ''
IF @WhereClause !=''
BEGIN
SET @sql = 'Select InDate,AccountID,OutDate From ' + @WhereClause
END
Exec (@sql)
GO
January 26, 2006 at 5:20 am
Kapil, even though this probably works for you, please take the time to read the excellent article Ray referred to. You are doing something that is usually not the right thing to do and is just evidence of design and usage issues that could be better solved.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply