SQL Server 2008 Stored Function Issue

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 Server 2008 Stored Function Issue

Author

Topic

RaveMaker
Starting Member

3 Posts

Posted - 2010-01-23 : 08:58:32

Hello
I want to create a dynamic sql functions.
i know i can't use @Variable in the COLUMN Name only in the Value
so i want to create a SELECT CASE

ALTER FUNCTION dbo.CheckField
(
@Field VARCHAR(MAX),
@Value VARCHAR(MAX)
)
RETURNS TABLE
AS
BEGIN
SELECT CASE @Field
WHEN 'UserName' THEN RETURN SELECT * FROM Users WHERE (UserName=@Value)
WHEN 'Password' THEN RETURN SELECT * FROM Users WHERE (Password=@Value)
WHEN 'EMail' THEN RETURN SELECT * FROM Users WHERE (EMail=@Value)
ELSE RETURN NULL
END

Can't save this function for some reason.

Kristen
Test

22859 Posts

Posted - 2010-01-23 : 11:14:28

[code]
RETURN
SELECT *
FROM Users
WHERE (@Field = 'UserName' AND UserName=@Value)
OR (@Field = 'Password' AND Password=@Value)
OR (@Field = 'EMail' AND EMail=@Value)
[/code]
However, do not use SELECT *, list the columns that your application needs to use

May Chan
Starting Member

6 Posts

Posted - 2010-01-23 : 12:06:12

Stored Routines (Procedures and Functions) are supported in version MySQL 5.0. ... Locking Issues ... 16990 views. Identifiers & Qualifiers

____________
http://www.microsoftoffice-2010.com/

RaveMaker
Starting Member

3 Posts

Posted - 2010-01-23 : 12:53:21

Thanx That was GREAT!
Final Function:
ALTER FUNCTION dbo.CheckField
(
@Field VARCHAR(MAX),
@Value VARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN SELECT * FROM Users
WHERE (@Field = 'UserName' AND UserName=@Value)
OR (@Field = 'Password' AND Password=@Value)
OR (@Field = 'EMail' AND EMail=@Value)

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 04:44:35

You do need to get rid of that "*" from the SELECT though ...

RaveMaker
Starting Member

3 Posts

Posted - 2010-01-24 : 14:00:30

I need al the columns in that table
why should i avoid using "*"?

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 14:19:15

SQL has to go look up what columns that means, which takes time (and may mean that the query cannot be cached)

If the application doesn't use all the columns then you are reducing performance by retrieving all the data, transporting it down the wire to the application, and then not using it.

If someone adds some columns in the future, which your application will know nothing about (and if it needs to then change both application and SQL to match), then the data for those extra columns will also be sent to the application. If those new columns are huge blobs of text that will cripple the performance of your system, and you will then be faced with going round and fixing all the SELECT * in your code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources