Stored Procedure or User Defined 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 2008 Forums

Transact-SQL (2008)

Stored Procedure or User Defined Function

Author

Topic

guessme_cat
Starting Member

6 Posts

Posted - 2011-01-08 : 10:30:42

Hey guys ! Hi :)

I am pretty confused, I wanted to check that the , column (c_id) of the NEW ROW being INSERTED in the table 'account' in the database 'GAMERS' have Propercase and not, to only allow Alphabets..

what will i have to do, so that it automatically convert when the Row is being Inserted in the table, will i have to create a stored procedure or create a user defined function so that it does the work automatically !! on every new row insertion

if i try to insert
SQlTeAM it will automatically update Sqlteam
s12qLtEAM It will automatically omit numbers and other chars and update Sqlteam

Help

Thanks Please help me

Please Help me !!!!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 10:53:40

How is the data being inserted now? By stored proc? from a staging table?

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 11:01:58

You could do something like this..although this is likely not optimal, but does what you ask



CREATE FUNCTION dbo.CleanMe
(@str VARCHAR(8000), @validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @validchars + ']%',@str) > 0
SET @str=REPLACE(@str, SUBSTRING(@str ,PATINDEX('%[^'
+ @validchars +']%',@str), 1) ,'')
RETURN @str
END

GO

Create Table #foo (Chars char(20) not null)
INSERT INTO #foo
SELECT 'sQ123Team' UNION
Select 'Sql/5434@teaM' UNION
Select 'sQlTeAM' UNION
Select 's12qltEAM'

SELECT UPPER(Left(dbo.CleanMe(Chars,'a-z^A-Z'),1)) + 
LOWER(RIGHT(dbo.CleanMe(Chars,'a-z^A-Z'),
len(dbo.CleanMe(Chars,'a-z^A-Z'))-1))
FROM #foo

drop table #foo
drop function dbo.CleanMe

Poor planning on your part does not constitute an emergency on my part.

guessme_cat
Starting Member

6 Posts

Posted - 2011-01-09 : 01:05:20

thanks f or the reply...:) I am not sure how it will be inserted i guess it will be inserted to the SQLServer from a program which connects to the server through ODBC and uses BDE..so i guess it will be updated by stored procedure isnt it? can make a trial and error to see if that works..

I know C++ and vb.net Php..but this language UDF looks strange :P it will be very good if you link me to some good tutorials of writing stored functions and some other basics also i have some doubts
1) when the rows will be inserted , will this function be automatically be running, checking and converting ?

2)What is this part of the code doing exactly


Create Table #foo (Chars char(20) not null)
INSERT INTO #foo
SELECT 'sQ123Team' UNION
Select 'Sql/5434@teaM' UNION
Select 'sQlTeAM' UNION
Select 's12qltEAM'

Moreover the datatype of the column is char(20) and please give brief detail where is the Column, table name the function is operating on ! !! I'll be thankful..

Please Help me !!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-09 : 01:20:32

see the below to understand UDFs

http://www.sqlteam.com/article/user-defined-functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

guessme_cat
Starting Member

6 Posts

Posted - 2011-01-09 : 01:26:10

visakh16 can you provide your email to any messenger we can Chat and solve the problem am confused to hell !>..once sorted will post the solution here

Please Help me !!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-09 : 01:29:29

Whats the portion you're having difficulty with? post it and we will sort it out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

guessme_cat
Starting Member

6 Posts

Posted - 2011-01-09 : 05:39:10

have some doubts
1) when the rows will be inserted , will this function be automatically be running, checking and converting ?

2)What is this part of the code doing exactly

Create Table #foo (Chars char(20) not null)
INSERT INTO #foo
SELECT 'sQ123Team' UNION
Select 'Sql/5434@teaM' UNION
Select 'sQlTeAM' UNION
Select 's12qltEAM'

Moreover the datatype of the column is char(20) and please give brief detail where is the Column, table name the function is operating on ! !! in the function dataguru gave

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-09 : 07:03:56

Yikes..the create table/insert into was sample data and a select statement which uses the function to show you the results. Create the function on your database, then when you are inserting the records pass the column of "bad data" through the function like the sample shows.

I had no way of knowing the length of your column...my #foo table was just for demonstrating the use of the function in SQL.

Or issue an update statement to clean the column

Update yourtablename
SET your column name = dbo.CleanMe(your column name )

Poor planning on your part does not constitute an emergency on my part.

guessme_cat
Starting Member

6 Posts

Posted - 2011-01-09 : 11:15:05

ya got it.. thanks but how can i make the insertion pass thru the function?, can't the function to clean the BAD Data be trigered automatically on new row insertion ?

The way to insert the data is not in my hands..

Please Help me !!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-09 : 13:03:32

quote:
Originally posted by guessme_cat

ya got it.. thanks but how can i make the insertion pass thru the function?, can't the function to clean the BAD Data be trigered automatically on new row insertion ?

The way to insert the data is not in my hands..

Please Help me !!!!

Nope the function cant be trigger automatically. you need to explicitly call it in insert as dataguru showed you

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

guessme_cat
Starting Member

6 Posts

Posted - 2011-01-10 : 02:44:06

sad then it makes no sense :(

Thanks for help guys ! aBig one :P

Please Help me !!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 11:43:44

you can include the logic in a trigger though for doing this change automatically. just use an INSTEAD OF INSERT trigger for this. It is definitely an overhead and not worth putting for these type of requirements

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources