Pls check my SQL Query - SQL Server Forums

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

Pls check my SQL Query

Author

Topic

thlaingwin
Starting Member

14 Posts

Posted - 2011-09-01 : 04:27:59

I have a table with fields : EID, EName, HomePH, MobilePH, MobilePH2.

I write the following statements in order to get Phone Number.

Create Function udf_PH (@Name varchar (35))
Returns varchar (100)
As
Begin
Declare @PH varchar (100)
If HomePH is not null
Select eName +'Home Phone Number is ' + HomePH
From Employee where ename=@Name
Else if MobilePH is not null
Select eName +'Mobile Phone Number is ' + MobilePH
From Employee where ename=@Name
Else if MobilePH2 is not null
Select eName +'Mobile Phone Number is ' + MobilePH2
From Employee where ename=@Name
Else
select eName+ 'doesn't have any phone'

Return @PH
End

After executing query, one error message is pop up.
Msg 102, Level 15, State 1, Procedure udf_PH, Line 12
Incorrect syntax near 'MobilePH2'.

Pls help me to fix. I have no more idea.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 04:34:00

i think what you're looking at is this


Create Function udf_PH (@Name varchar (35))
Returns varchar (100)
As
Begin
Declare @PH varchar (100)
 Select @PH  =eName + COALESCE(NULLIF(COALESCE('Home Phone Number is ' + HomePH + ' ','')
+ COALESCE('Mobile Phone Number is ' + MobilePH + ' ','')
+COALESCE('Mobile Phone Number is ' + MobilePH2,''),''),'doesn't have any phone')
From Employee where ename=@Name

Return @PH
End

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

thlaingwin
Starting Member

14 Posts

Posted - 2011-09-01 : 05:19:44

Create Function udf_PH (@Name varchar (35))
Returns varchar (100)
As
Begin
Declare @PH varchar (100)
Select @PH =eName + COALESCE(NULLIF(COALESCE('Home Phone Number is ' + HomePH))
+COALESCE('Mobile Phone Number is ' + MobilePH)
+COALESCE('Mobile Phone Number is ' + MobilePH2),'doesnot have any phone')
From Employee where ename=@Name

Return @PH
End

I modify and execute query. But some error

Msg 102, Level 15, State 1, Procedure udf_PH, Line 6
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 05:22:30

[code]
Create Function udf_PH (@Name varchar (35))
Returns varchar (100)
As
Begin
Declare @PH varchar (100)
Select @PH =eName + COALESCE(NULLIF(
COALESCE('Home Phone Number is ' + HomePH,'')
+COALESCE('Mobile Phone Number is ' + MobilePH,'')
+COALESCE('Mobile Phone Number is ' + MobilePH2,''),''),'doesnot have any phone')
From Employee where ename=@Name

Return @PH
End
[/code]

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

thlaingwin
Starting Member

14 Posts

Posted - 2011-09-01 : 05:42:48

Thanks Sir,

I got it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 07:29:45

welcome

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

thlaingwin
Starting Member

14 Posts

Posted - 2011-09-01 : 22:06:53

I notice some after using this sql statement.

Actually what I want to mention is only one phone. whatever employee has homeph or mobileph or mobileph2. One Phone is enough

The result is combine when one Employee has more than one phone.

Should it be 'Else if' instead of '+'?

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-09-01 : 22:49:31

So, you want a function to return the first number found, how about something like this (untested):


Create Function udf_PH (@Name varchar (35))
Returns varchar (100)
As
Begin
Declare @PH varchar (100);

With employeePhones (PhoneOrder, PhoneType, PhoneNumber)
As (
Select 1, 'Home Phone', h.HomePH From dbo.Employee h Where h.eName = @Name
Union All
Select 2, 'Mobile Phone 1', m1.HomePH From dbo.Employee m1 Where m1.eName = @Name
Union All
Select 3, 'Mobile Phone 2', m2.HomePH From dbo.Employee m2 Where m2.eName = @Name
)
Select Top 1 @PH = PhoneNumber From employeePhones Where PhoneNumber Is Not Null Order By PhoneOrder;
Return @PH
End

Now, I would change this to an iTVF instead and then use CROSS APPLY to get the number I wanted. Something like:


Create Function udf_PH (@Name varchar (35))
Returns table
As
Return
Select 1 As PhoneOrder, 'Home Phone' As PhoneType, h.HomePH As PhoneNumber From dbo.Employee h Where h.eName = @Name
Union All
Select 2 As PhoneOrder, 'Mobile Phone 1' As PhoneType, m1.HomePH As PhoneNumber From dbo.Employee m1 Where m1.eName = @Name
Union All
Select 3 As PhoneOrder, 'Mobile Phone 2' As PhoneType, m2.HomePH As PhoneNumber From dbo.Employee m2 Where m2.eName = @Name
End

Then, using the function would be something like this:

Select ..., ph.PhoneNumber
From {tables}
Cross Apply (Select Top 1 PhoneNumber
From dbo.udf_PH({name column from other table})
Where PhoneNumber Is Not Null
Order By PhoneOrder) As ph
Where ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 23:57:49

if you want only one phone use like


Create Function udf_PH (@Name varchar (35))
Returns varchar (100)
As
Begin
Declare @PH varchar (100)
Select @PH =eName + COALESCE(NULLIF(
COALESCE('Home Phone Number is ' + HomePH,'Mobile Phone Number is ' + MobilePH,'Mobile Phone Number is ' + MobilePH2,''),''),'doesnot have any phone')
From Employee where ename=@Name

Return @PH
End

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

thlaingwin
Starting Member

14 Posts

Posted - 2011-09-02 : 02:02:02

Thanks Again!
I am very beginner in sql. I am just studying now. Hope I get more help and advise about sql in future.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 03:08:12

you're welcome

yep...sure..Thats what we're here for

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

thlaingwin
Starting Member

14 Posts

Posted - 2011-09-08 : 07:48:34

Hi,

I have product table with following fields.
PID int not null,
PName varchar(50) not null,
QTY int null,
Price money not null,
constraint PK_Pro
primary key (PID)

And also I have tracking table with following fields.
SrNo int identity(1,1) not null,
MWhen smalldatetime not null,
Who varchar(50) not null,
OldPrice money not null,
Updateprice money not null,
constraint PK_tra
primary key (SrNo)

I want to update automatically tracking table when some one (from client) modify price value.

I write sql query as bellow:
create trigger trg_utracking
on product
After update
as
declare @Oldprice money
declare @newprice money

update t
set @oldprice =(select price from deleted)
set @newprice =(select price from inserted)
set Mwhen=getdate()
set who=suser_sname()
from tracking as t

After I execute query, I saw some error message as mentioned below:

Msg 102, Level 15, State 1, Procedure trg_utracking, Line 11
Incorrect syntax near '='.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 02:28:35

i think you trigger should be this??


create trigger trg_utracking
on product
After update
as

insert into tracking (MyWhen,Who,OldPrice,NewPrice)
select getdate(),suser_sname(),d.price,i.price
from deleted d
inner join inserted i
on i.PID = d.PID

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

thlaingwin
Starting Member

14 Posts

Posted - 2011-09-11 : 06:33:20

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-11 : 11:35:24

welcome

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

thlaingwin
Starting Member

14 Posts

Posted - 2011-09-27 : 05:05:39

Hi,

I create one table. And I would like to set up more than one default.
e.g. create table test (
ID int not null,
Name varchar(30) not null,
Department varchar(30)not null default 'Admin' or 'Marketing'
)

I can't create like this. What is correct way for department column?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 05:12:57

whats the purpose of that? under what condition you want value to admin and when you want it to be Marketing?

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

thlaingwin
Starting Member

14 Posts

Posted - 2011-09-27 : 22:14:39

I want to specific value in department column as there are only five departments. I don't want to add other value apart from that five departments.

Should I create separate table for department?

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-27 : 22:28:40

You might want to add a CHECK constraint. Something like this:

create table testcheck
(
ID int not null,
[Name] varchar(30) not null,
Department varchar(30)not null,
CONSTRAINT chk_dept CHECK (Department='Admin' or Department='Marketing')
)

-- these 2 inserts will work
Insert into testCheck values (1, 'vadivel', 'Admin')
Insert into testCheck values (1, 'vadivel', 'marketing')

--this will throw an error
Insert into testCheck values (1, 'vadivel', 'Finance')

Best Regards
Vadivel

http://vadivel.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 23:07:12

quote:
Originally posted by thlaingwin

I want to specific value in department column as there are only five departments. I don't want to add other value apart from that five departments.

Should I create separate table for department?

thats fine
but if you want to make it default or computed you need to specify conditions and also value to be populated for each.
Otherwise that check constraint suggestion would suffice

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

thlaingwin
Starting Member

14 Posts

Posted - 2011-09-30 : 00:31:36

Thanks to All!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources