DAL in a procedure or function

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

SQL Server 2005 Forums

Transact-SQL (2005)

DAL in a procedure or function

Author

Topic

axt
Starting Member

5 Posts

Posted - 2009-08-19 : 03:11:07

Hello Folks,

is it possible to place DAL-Statements in a function or procedure?

The function/procedure i want to create is complexer, but first i tried this simple one:


CREATE PROCEDURE dbo.testproc(@table varchar(100), @role varchar(100))
AS
	grant select on @table to @role;
GO

And got the message: "Incorrect syntax near '@table'".

Then i tried this one:


create FUNCTION dbo.grantage(@table varchar(100), @role varchar(100))
RETURNS numeric(1)
AS
BEGIN
	grant select on @table to @role;
RETURN 0
END

And got the same error message.

Your help is appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-19 : 03:46:11

[code]CREATE PROCEDURE dbo.testproc(@table varchar(100), @role varchar(100))
AS
EXEC('grant select on '+@table+' to '+@role);
GO
[/code]

Madhivanan

Failing to plan is Planning to fail

axt
Starting Member

5 Posts

Posted - 2009-08-19 : 06:08:32

Thanks a lot. This works.

The next problem one may have is escaping. Topics about escaping can be found in internet pretty easely, but i mention an example of it:

Original:


sp_addrolemember 'db_owner', 'db_own';

A procedure:


create PROCEDURE dbo.testproc2(@role varchar(100), @user varchar(100))
AS	
	exec('sp_addrolemember '''+ @role +''''+ ',' + '''' + @user + '''');
GO

Now i'm satisfied an can change this procedure according to my task.

Greets
axt

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-19 : 06:44:44

Refer this to know how to use single quotes
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

axt
Starting Member

5 Posts

Posted - 2009-08-19 : 06:53:57

quote:

Refer this to know how to use single quotes
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Even if one has no problems with escaping anymore, the article is woth to see.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources