SQL function using dynamic sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL function using dynamic sql

Author

Topic

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-21 : 08:09:39

Hi all

I am trying to pass a column name as a variable in a function but it will not let me. I am sure it is something stupid. I know that you cannot pass a column in a normal select statement as a variable. You have to execute it dynamically, however you cannot use the exec in a function. Please see code below

ALTER FUNCTION dbo.GetData(@ColumnName varchar(50))

RETURNS Decimal(18,4)

AS

BEGIN

DECLARE @Value Decimal(18,4)

SET @Value = (SELECT + @ColumnName + FROM Policy WHERE Grade = 'Revised') -- Tried this way, does not work need to use Exec

SET @Value = Exec('SELECT '+ @ColumnName + ' FROM Policy WHERE Grade = 'Revised') -- Tried this, cannot use exec in a function

RETURN @Value

END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 08:14:39

It is not possible to use dynamic sql inside a function
Use stored procedure with output parameter

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-21 : 08:22:41

Better yet, normalize your database and then you won't need any dynamic SQL at all.

- Jeff
http://weblogs.sqlteam.com/JeffS

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-21 : 08:23:28

Hi thanks I thought this was the reason. Would it be possible for you to give me an example

I appreciate the help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 08:42:37

In and out of Dynamic SQL
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-21 : 08:44:43

Should be something like.


CREATE PROCEDURE dbo.GetData
	@ColumnName varchar(50)
	, @returnValue <validDatatypeHere> OUTPUT

AS BEGIN

sp_executeSql
	N'SELECT @returnVar = ['+ @ColumnName + '] FROM Policy WHERE Grade = ''Revised'''
	, N'@returnVar <validDatatypeHere> OUTPUT'
	, @returnValue OUTPUT
END

GO

-- Call it with
DECLARE @outputVar <validDatatypeHere>

EXEC dbo.GetData 'column1', @outputVar OUTPUT

-- Edited because my previous posted code was horses**t

-------------
Charlie

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-21 : 08:44:53

Hi

I cannot do that as there are tables that have dynamic columns. The application I am creating imports data to tables and generates columns depending on the import. Each client will import there own named columns

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-21 : 08:49:11

quote:
Originally posted by madhivanan

In and out of Dynamic SQL
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Yes -- totally and utterly yes. follow the link, bookmark it, read it. Read it again, refer.

-------------
Charlie

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-21 : 09:11:40

Hi Charlie

This is my actual code. I changed it to match yours but it gives me an error

Running [dbo].[A_GetBasicEsc] ( @ColumnName = A, @returnValue = <DEFAULT> ).

Procedure or function 'A_GetBasicEsc' expects parameter '@returnValue', which was not supplied.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[A_GetBasicEsc].

ALTER PROCEDURE dbo.A_GetBasicEsc

@ColumnName varchar(50),
@returnValue Decimal(18,4)

AS

BEGIN
DECLARE @SQL VARCHAR(300)

SET @SQL = N'SELECT @returnVar = ' + @ColumnName + ' FROM BasicEsc_tbl WHERE RIGHT(CONVERT(varchar(50),[Date],6),6) = RIGHT(CONVERT(varchar(50),GETDATE(),6),6)'

EXEC sp_executeSql @SQL, N'@returnVar Decimal(18,4) OUTPUT', @returnValue OUTPUT

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 09:33:13

why are you passing <DEFAULT> as value to second param? if you want it to take default value, just declare it as optional and dont pass value at all.declare like

@returnValue Decimal(18,4)=<defaultvalue>
and execute sp like
dbo].[A_GetBasicEsc] @ColumnName = 'A'

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-21 : 10:02:07

Hi Charlie

Lets try this again. The GetData SP is created correctly,however if I execute the SP it asks me for the values. I then supply the column value

CODE
ALTER PROCEDURE dbo.A_GetBasicEsc
@ColumnName varchar(50),
@returnValue Decimal(18,4)= Null
AS
BEGIN
DECLARE @SQL VARCHAR(300)
SET @SQL = N'SELECT @returnVar = [' + @ColumnName + '] FROM BasicEsc_tbl WHERE RIGHT(CONVERT(varchar(50),[Date],6),6) = RIGHT(CONVERT(varchar(50),GETDATE(),6),6)'
EXEC sp_executeSql @SQL, N'@returnVar Decimal(18,4) OUTPUT', @returnValue OUTPUT
END

Error Msg
Running [dbo].[A_GetBasicEsc] ( @ColumnName = A, @returnValue = <NULL> ).

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[A_GetBasicEsc].

If I call the SP from another SP I get another error

CODE
ALTER PROCEDURE dbo.StoredProcedure9
AS
DECLARE @outputVar Decimal(18,4)
EXEC dbo.GetData 'A', @outputVar OUTPUT
RETURN

ERROR
Procedure or function GetData has too many arguments specified.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[StoredProcedure9].

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-21 : 10:07:05

Ok -- you were declaring @sql as a varchar -- needs to be NVARCHAR for sp_executeSql

Try,


ALTER PROCEDURE dbo.A_GetBasicEsc
	@ColumnName varchar(50),
	@returnValue Decimal(18,4) = Null OUTPUT
AS
BEGIN
	DECLARE @SQL NVARCHAR(4000)
	SET @SQL = 'SELECT @returnVar = [' + @ColumnName + '] FROM BasicEsc_tbl WHERE RIGHT(CONVERT(varchar(50),[Date],6),6) = RIGHT(CONVERT(varchar(50),GETDATE(),6),6)'
	EXEC sp_executeSql @SQL, N'@returnVar Decimal(18,4) OUTPUT', @returnValue OUTPUT
END

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-21 : 10:09:38

& you weren't declaring the variable @returnValue in your procedure deceleration as OUTPUT

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-21 : 10:11:22

Did you follow this link also?

In and out of Dynamic SQL
www.sommarskog.se/dynamic_sql.html

If not I cannot recommend it enough. It is easy to read and will give you *everything* you need to know about dynamic sql.

-------------
Charlie

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-21 : 10:24:02

Hi Charlie it now executes but does not pass the value

ALTER PROCEDURE dbo.StoredProcedure9
AS
DECLARE @outputVar Decimal(18,4)
EXEC A_GetBasicEsc 'A', @outputVar OUTPUT
RETURN @outputVar

-----------------------------------
Running [dbo].[StoredProcedure9].

No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[StoredProcedure9].

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-21 : 10:29:37

are you sure that the select statement actually works.

Try printing the string in your stored proc before passing it to sp_executeSql and then try running that manually to see if it returns a value. Can you post the output of that print statement here?

Can you also try this in a query analyser window?


DECLARE @outputVar Decimal(18,4)
SET @outputVar = 010101.1010

SELECT @outputVar

EXEC A_GetBasicEsc 'A', @outputVar OUTPUT

SELECT @outputVar

To see if it works not inside you *next* nested stored proc.

-------------
Charlie

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-21 : 10:36:31

Hi Charlie

If I change this

SET @SQL = 'SELECT @returnVar = A FROM BasicEsc_tbl'

To this

SET @SQL = 'SELECT A FROM BasicEsc_tbl'

I get what im looking for

A
----------------
0
0.0575
0.0575
0.0575
0.072
0.072
0.072
0.093
0.093
0.093
0.112
0.112
No rows affected.
(12 row(s) returned)
@returnValue = <NULL>
@RETURN_VALUE = 0
Finished running [dbo].[A_GetBasicEsc].

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-21 : 10:38:38

This also works

SET @SQL = 'SELECT '+@ColumnName+' FROM BasicEsc_tbl'

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-21 : 10:42:33

Can you post the complete query please? including the where clause with the conditions.

I assume that you are trying to bring back only 1 value in your variable rather than a collection?

Just add in the line

PRINT @sql

before the sp_executeSql call and when you EXEC the stored proc in your query window it will print the complete select statement.

-------------
Charlie

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-21 : 10:51:54

Hi Charlie

Yes I am trying to return one value. If I remove the @returnVar from

SET @SQL = 'SELECT '+@ColumnName+' it works perfectly and returns the value I want. I think the problem lies in EXEC sp_executeSql @SQL, N'@returnVar Decimal(18,4) OUTPUT

CODE
ALTER PROCEDURE dbo.A_GetBasicEsc
@ColumnName varchar(50),
@returnValue Decimal(18,4) = Null OUTPUT
AS
BEGIN
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT @returnVar = '+@ColumnName+' FROM BasicEsc_tbl WHERE RIGHT(CONVERT(varchar(50),[Date],6),6) = RIGHT(CONVERT(varchar(50),GETDATE(),6),6)'
EXEC sp_executeSql @SQL, N'@returnVar Decimal(18,4) OUTPUT', @returnValue OUTPUT
END

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-21 : 11:13:27

Hi again.

I've built some test cases to try and work out what your dynamic sql is doing.

Am I right in thinking that you are trying to bring back a value for the current month? That's what I think you are trying to do with your WHERE clause


RIGHT(CONVERT(varchar(50),[Date],6),6) = RIGHT(CONVERT(varchar(50),GETDATE(),6),6)'

I've set up the following for a test You can run it on a test database to try it out -- *DO NOT RUN THIS ON YOUR LIVE SITE BECAUSE IT DROPS THE TABLE YOU ARE QUERYING*


DROP TABLE BasicEsc_tbl
CREATE TABLE BasicEsc_tbl (
	[field1] DECIMAL (18,4)
	, [date] DATETIME
	)

INSERT BasicEsc_tbl
      SELECT   100.00, '2008-08-01T00:00:00'
UNION SELECT    50.23, '2007-08-01T00:00:00'
UNION SELECT 12124.23, '2008-09-01T00:00:00' 
GO

ALTER PROCEDURE dbo.A_GetBasicEsc
	@ColumnName varchar(50),
	@returnValue Decimal(18,4) = NULL OUTPUT
AS BEGIN
	DECLARE @SQL NVARCHAR(4000)
	SET @SQL = 'SELECT @returnVar = '+@ColumnName+' FROM BasicEsc_tbl WHERE RIGHT(CONVERT(varchar(50),[Date],6),6) = RIGHT(CONVERT(varchar(50),GETDATE(),6),6)'
	PRINT @sql
	EXEC sp_executeSql @SQL, N'@returnVar Decimal(18,4) OUTPUT', @returnValue OUTPUT
END
GO

ALTER PROCEDURE dbo.A_GetBasicEsc_DATEDIFF
	@ColumnName varchar(50),
	@returnValue Decimal(18,4) = NULL OUTPUT
AS BEGIN
	DECLARE @SQL NVARCHAR(4000)
	SET @SQL = 'SELECT @returnVar = '+@ColumnName+' FROM BasicEsc_tbl WHERE DATEDIFF(MONTH, [date], GETDATE()) = 0'
	PRINT @sql
	EXEC sp_executeSql @SQL, N'@returnVar Decimal(18,4) OUTPUT', @returnValue OUTPUT
END
GO

DECLARE @outputVariable DECIMAL(18,4)

EXEC dbo.A_GetBasicEsc 'field1', @outputVariable OUTPUT

SELECT @outputVariable

SET @outputVariable = 0

EXEC dbo.A_GetBasicEsc_DATEDIFF 'field1', @outputVariable OUTPUT

SELECT @outputVariable

This correctly selects 100.00 as the @outputVariable So I think there is a problem with your data (unless my test data is nothing like yours)

-------------
Charlie

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources