Styjun

Generate and graph the Recamán Sequence

Is there a category where products don't exist because uniqueness fails?

Miss Toad and her frogs

Averting Real Women Don’t Wear Dresses

Reverse of diffraction

Is this hogweed?

How hard is it to sell a home which is currently mortgaged?

Golf the smallest circle!

What is "oversubscription" in Networking?

Is there reliable evidence that depleted uranium from the 1999 NATO bombing is causing cancer in Serbia?

Symbol for "not absolutely continuous" in Latex

Loss of majority in Westminster

Prime parity peregrination

Wrong corporate name on employment agreement

How did researchers use to find articles before the Internet and the computer era?

Who gets an Apparition licence?

Why won't the ground take my seed?

Can Access Fault Exceptions of the MC68040 caused by internal access faults occur in normal situations?

Does “comme on était à New York” mean “since” or “as though”?

The Confused Alien

How exactly is a normal force exerted, at the molecular level?

3D nonogram, beginner's edition

Can a single server be associated with multiple domains?

Could human civilization live 150 years in a nuclear-powered aircraft carrier colony without resorting to mass killing/ cannibalism?

How can write function inside execute key in sql

How can I remove duplicate rows?How do I perform an IF…THEN in an SQL SELECT?How to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How can foreign key constraints be temporarily disabled using T-SQL?How can I list all foreign keys referencing a given table in SQL Server?Insert results of a stored procedure into a temporary tableFunction vs. Stored Procedure in SQL ServerHow can I do an UPDATE statement with JOIN in SQL?How do I UPDATE from a SELECT in SQL Server?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

Create Function fnRMatrixColorGet1(
@RMID varchar(20)
)
returns varchar(100)

as
begin

EXEC (N'SELECT ' + 'C'+@RMID + ' FROM vwemployeeget where empid='+@RMID) 
return 
end

sql-server tsql dynamic-sql stored-functions

share|improve this question

edited Mar 25 at 15:11

a_horse_with_no_name

318k5050 gold badges488488 silver badges592592 bronze badges

asked Mar 25 at 12:28

Rajeshwar PRajeshwar P

1

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Which dbms are you using? (That code is product specific.)
  
  – jarlh
  Mar 25 at 12:31
  

  
  
  
  


• 
  
  
  

  
  4
  

  
  
  
  
  
  

  
  
  Functions in SQL Server cannot execute dynamic SQL.
  
  – Gordon Linoff
  Mar 25 at 12:34
  

  
  
  
  

add a comment | 

0

Create Function fnRMatrixColorGet1(
@RMID varchar(20)
)
returns varchar(100)

as
begin

EXEC (N'SELECT ' + 'C'+@RMID + ' FROM vwemployeeget where empid='+@RMID) 
return 
end

sql-server tsql dynamic-sql stored-functions

share|improve this question

edited Mar 25 at 15:11

a_horse_with_no_name

318k5050 gold badges488488 silver badges592592 bronze badges

asked Mar 25 at 12:28

Rajeshwar PRajeshwar P

1

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Which dbms are you using? (That code is product specific.)
  
  – jarlh
  Mar 25 at 12:31
  

  
  
  
  


• 
  
  
  

  
  4
  

  
  
  
  
  
  

  
  
  Functions in SQL Server cannot execute dynamic SQL.
  
  – Gordon Linoff
  Mar 25 at 12:34
  

  
  
  
  

add a comment | 

Create Function fnRMatrixColorGet1(
@RMID varchar(20)
)
returns varchar(100)

as
begin

EXEC (N'SELECT ' + 'C'+@RMID + ' FROM vwemployeeget where empid='+@RMID) 
return 
end

sql-server tsql dynamic-sql stored-functions

share|improve this question

edited Mar 25 at 15:11

a_horse_with_no_name

318k5050 gold badges488488 silver badges592592 bronze badges

asked Mar 25 at 12:28

Rajeshwar PRajeshwar P

1

Create Function fnRMatrixColorGet1(
@RMID varchar(20)
)
returns varchar(100)

as
begin

EXEC (N'SELECT ' + 'C'+@RMID + ' FROM vwemployeeget where empid='+@RMID) 
return 
end

sql-server tsql dynamic-sql stored-functions

sql-server tsql dynamic-sql stored-functions

share|improve this question

edited Mar 25 at 15:11

a_horse_with_no_name

318k5050 gold badges488488 silver badges592592 bronze badges

asked Mar 25 at 12:28

Rajeshwar PRajeshwar P

1

share|improve this question

edited Mar 25 at 15:11

a_horse_with_no_name

318k5050 gold badges488488 silver badges592592 bronze badges

asked Mar 25 at 12:28

Rajeshwar PRajeshwar P

1

edited Mar 25 at 15:11

a_horse_with_no_name

318k5050 gold badges488488 silver badges592592 bronze badges

edited Mar 25 at 15:11

a_horse_with_no_name

318k5050 gold badges488488 silver badges592592 bronze badges

asked Mar 25 at 12:28

Rajeshwar PRajeshwar P

1

asked Mar 25 at 12:28

Rajeshwar PRajeshwar P

1

1 Answer
1

active

oldest

votes

0

As Gordon wrote in the comments, user defined functions in SQL Server can't execute dynamic SQL.

From Create User-defined Functions:

• User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.

However, you can create a stored procedure to do that:

CREATE PROCEDURE stpRMatrixColorGet1
(
 @RMID varchar(20)
 @MatrixColor varchar(100) OUTPUT
)
AS

DECLARE @Sql nvarchar(4000),
 @Column sysname = N'C' + @RMID;

-- White list column name since it can't be parameterized
IF EXISTS
(
 SELECT 1
 FROM Information_Schema.Columns
 WHERE Table_Name = 'vwemployeeget'
 AND Column_Name = @Column
)
BEGIN

 SET @SQL = N'SELECT @MatrixColor = QUOTENAME('+ @Column +') FROM vwemployeeget where empid = @RMID'

 -- Safely execute dynamic SQL using sp_ExecuteSql
 EXEC sp_ExecuteSql 
 @Sql, 
 N'@RMID varchar(20), @MatrixColor varchar(100) OUTPUT', 
 @RMID, 
 @MatrixColor OUTPUT
END

share|improve this answer

edited Mar 25 at 16:06

answered Mar 25 at 16:01

Zohar PeledZohar Peled

59.2k77 gold badges3535 silver badges7777 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank for Reply
  
  – Rajeshwar P
  Mar 26 at 13:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Glad to help :-)
  
  – Zohar Peled
  Mar 27 at 9:52
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55337800%2fhow-can-write-function-inside-execute-key-in-sql%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

As Gordon wrote in the comments, user defined functions in SQL Server can't execute dynamic SQL.

From Create User-defined Functions:

• User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.

However, you can create a stored procedure to do that:

CREATE PROCEDURE stpRMatrixColorGet1
(
 @RMID varchar(20)
 @MatrixColor varchar(100) OUTPUT
)
AS

DECLARE @Sql nvarchar(4000),
 @Column sysname = N'C' + @RMID;

-- White list column name since it can't be parameterized
IF EXISTS
(
 SELECT 1
 FROM Information_Schema.Columns
 WHERE Table_Name = 'vwemployeeget'
 AND Column_Name = @Column
)
BEGIN

 SET @SQL = N'SELECT @MatrixColor = QUOTENAME('+ @Column +') FROM vwemployeeget where empid = @RMID'

 -- Safely execute dynamic SQL using sp_ExecuteSql
 EXEC sp_ExecuteSql 
 @Sql, 
 N'@RMID varchar(20), @MatrixColor varchar(100) OUTPUT', 
 @RMID, 
 @MatrixColor OUTPUT
END

share|improve this answer

edited Mar 25 at 16:06

answered Mar 25 at 16:01

Zohar PeledZohar Peled

59.2k77 gold badges3535 silver badges7777 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank for Reply
  
  – Rajeshwar P
  Mar 26 at 13:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Glad to help :-)
  
  – Zohar Peled
  Mar 27 at 9:52
  

  
  
  
  

add a comment | 

0

As Gordon wrote in the comments, user defined functions in SQL Server can't execute dynamic SQL.

From Create User-defined Functions:

• User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.

However, you can create a stored procedure to do that:

CREATE PROCEDURE stpRMatrixColorGet1
(
 @RMID varchar(20)
 @MatrixColor varchar(100) OUTPUT
)
AS

DECLARE @Sql nvarchar(4000),
 @Column sysname = N'C' + @RMID;

-- White list column name since it can't be parameterized
IF EXISTS
(
 SELECT 1
 FROM Information_Schema.Columns
 WHERE Table_Name = 'vwemployeeget'
 AND Column_Name = @Column
)
BEGIN

 SET @SQL = N'SELECT @MatrixColor = QUOTENAME('+ @Column +') FROM vwemployeeget where empid = @RMID'

 -- Safely execute dynamic SQL using sp_ExecuteSql
 EXEC sp_ExecuteSql 
 @Sql, 
 N'@RMID varchar(20), @MatrixColor varchar(100) OUTPUT', 
 @RMID, 
 @MatrixColor OUTPUT
END

share|improve this answer

edited Mar 25 at 16:06

answered Mar 25 at 16:01

Zohar PeledZohar Peled

59.2k77 gold badges3535 silver badges7777 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank for Reply
  
  – Rajeshwar P
  Mar 26 at 13:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Glad to help :-)
  
  – Zohar Peled
  Mar 27 at 9:52
  

  
  
  
  

add a comment | 

As Gordon wrote in the comments, user defined functions in SQL Server can't execute dynamic SQL.

From Create User-defined Functions:

• User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.

However, you can create a stored procedure to do that:

CREATE PROCEDURE stpRMatrixColorGet1
(
 @RMID varchar(20)
 @MatrixColor varchar(100) OUTPUT
)
AS

DECLARE @Sql nvarchar(4000),
 @Column sysname = N'C' + @RMID;

-- White list column name since it can't be parameterized
IF EXISTS
(
 SELECT 1
 FROM Information_Schema.Columns
 WHERE Table_Name = 'vwemployeeget'
 AND Column_Name = @Column
)
BEGIN

 SET @SQL = N'SELECT @MatrixColor = QUOTENAME('+ @Column +') FROM vwemployeeget where empid = @RMID'

 -- Safely execute dynamic SQL using sp_ExecuteSql
 EXEC sp_ExecuteSql 
 @Sql, 
 N'@RMID varchar(20), @MatrixColor varchar(100) OUTPUT', 
 @RMID, 
 @MatrixColor OUTPUT
END

share|improve this answer

edited Mar 25 at 16:06

answered Mar 25 at 16:01

Zohar PeledZohar Peled

59.2k77 gold badges3535 silver badges7777 bronze badges

CREATE PROCEDURE stpRMatrixColorGet1
(
 @RMID varchar(20)
 @MatrixColor varchar(100) OUTPUT
)
AS

DECLARE @Sql nvarchar(4000),
 @Column sysname = N'C' + @RMID;

-- White list column name since it can't be parameterized
IF EXISTS
(
 SELECT 1
 FROM Information_Schema.Columns
 WHERE Table_Name = 'vwemployeeget'
 AND Column_Name = @Column
)
BEGIN

 SET @SQL = N'SELECT @MatrixColor = QUOTENAME('+ @Column +') FROM vwemployeeget where empid = @RMID'

 -- Safely execute dynamic SQL using sp_ExecuteSql
 EXEC sp_ExecuteSql 
 @Sql, 
 N'@RMID varchar(20), @MatrixColor varchar(100) OUTPUT', 
 @RMID, 
 @MatrixColor OUTPUT
END

share|improve this answer

edited Mar 25 at 16:06

answered Mar 25 at 16:01

Zohar PeledZohar Peled

59.2k77 gold badges3535 silver badges7777 bronze badges

answered Mar 25 at 16:01

Zohar PeledZohar Peled

59.2k77 gold badges3535 silver badges7777 bronze badges

answered Mar 25 at 16:01

Zohar PeledZohar Peled

59.2k77 gold badges3535 silver badges7777 bronze badges