Creating a Stored Proceduree - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Creating a Stored Proceduree

Author

Topic

tomex1
Starting Member

47 Posts

Posted - 2008-07-25 : 07:49:05

Hello all,
It's been ages I used procedures so I have forgotten how it works. I am trying to run a Stored procedure from a web application. The challenge I have got is - before using the stored procedure to insert into another table, I need to query a VIEW to get the uniqueid of a contact.

This uniqueid is one of the prameters that the procedure will insert into the destination table. Please help me with the syntax

Cheers,
Tomex

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 07:58:44

didnt get what you're asking for. do you mean passing a value from view into stored procedure as a parameter?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-07-25 : 08:04:41

If it's all in a Stored procedure you could look at doing something like , makeing sure you add all the rigfht columns:
DECLARE @myID INT;
SET @myID = (SELECT id FROM MyView WHERE name = @name)
INSERT INTO atable
SELRCT @myID

This can be done as a transaction .
Alternatively , you could split this up into 2 procedures

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

tomex1
Starting Member

47 Posts

Posted - 2008-07-28 : 06:26:45

quote:
Originally posted by jackv

If it's all in a Stored procedure you could look at doing something like , makeing sure you add all the rigfht columns:
DECLARE @myID INT;
SET @myID = (SELECT id FROM MyView WHERE name = @name)
INSERT INTO atable
SELRCT @myID

This can be done as a transaction .
Alternatively , you could split this up into 2 procedures

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Hi Jack,
I have tried what you said but it's inserting a null value for the @contactid which shouldn't be. Please find the SQL statement I used below:

Create procedure test2
@createtime datetime,
@createuser varchar(16),
@duration numeric(10,0),
@subject varchar(50),
@uniqueid varchar(16)

AS
declare @contactid varchar(16)
set @contactid = (select UNIQUEID from wce_contact where contact = '@createuser')

insert into wce_activity (UNIQUEID, CREATEUSER, DURATION, SUBJECT)

VALUES (@uniqueid, @contactid, @duration, @subject)

This is the syntax I used to execute the procedure

exec test2 '28 JUL 08','Segun Palmer','80','Test..........','))fjjjkkkkdkkkkk'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-28 : 06:30:54

Replace

set @contactid = (select UNIQUEID from wce_contact where contact = '@createuser')

with

set @contactid = (select UNIQUEID from wce_contact where contact = @createuser)

Madhivanan

Failing to plan is Planning to fail

tomex1
Starting Member

47 Posts

Posted - 2008-07-28 : 06:40:07

quote:
Originally posted by madhivanan

Replace

set @contactid = (select UNIQUEID from wce_contact where contact = '@createuser')

with

set @contactid = (select UNIQUEID from wce_contact where contact = @createuser)

Madhivanan

Failing to plan is Planning to fail

Hi Madhivanan,
Many thanks for this. It worked! I have just one more issue and it's with the createdate. I get this error message when I try to insert a date:

"Error converting data type varchar to datetime."

What do you reckon I do?

Thanks again

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-28 : 06:51:23

Express date value in YYYYMMDD format

Madhivanan

Failing to plan is Planning to fail

tomex1
Starting Member

47 Posts

Posted - 2008-07-28 : 06:54:36

quote:
Originally posted by madhivanan

Express date value in YYYYMMDD format

Madhivanan

Failing to plan is Planning to fail

Hi,
I have sourced that out. Also, can you tell me how to execute a procedure if I am trying to insert into two different tables?

I'll show you what I mean. The SQL below is a procedure and it's meant to insert into two tables. How do I execute it?

CREATE procedure test2
@createtime datetime,
@createuser varchar(16),
@duration numeric(10,0),
@subject varchar(50),
@uniqueid varchar(16),
@etable varchar(100),
@utable varchar(100)

AS
declare @contactid varchar(16)
set @contactid = (select UNIQUEID from wce_contact where contact = @createuser)

insert into wce_linkto (LEntityID, LETableName, LUniqueID, LUTableName)

VALUES (@contactid, @etable, @uniqueid, @utable)

insert into wce_activity (UNIQUEID, CREATEUSER, CREATETIME, DURATION, SUBJECT)

VALUES (@uniqueid, @contactid, @createtime, @duration, @subject)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-28 : 07:00:02

You need to supply all the parameter values. Thats it

Madhivanan

Failing to plan is Planning to fail

tomex1
Starting Member

47 Posts

Posted - 2008-07-28 : 07:03:13

quote:
Originally posted by madhivanan

You need to supply all the parameter values. Thats it

Madhivanan

Failing to plan is Planning to fail

Thanks madhivanan. But how does it identify which is going to wce_linkto table and which is going to wce_activity table?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-28 : 07:05:57

quote:
Originally posted by tomex1

quote:
Originally posted by madhivanan

You need to supply all the parameter values. Thats it

Madhivanan

Failing to plan is Planning to fail

Thanks madhivanan. But how does it identify which is going to wce_linkto table and which is going to wce_activity table?

You have already specified them in the INSERT statement

Madhivanan

Failing to plan is Planning to fail

tomex1
Starting Member

47 Posts

Posted - 2008-07-28 : 08:35:42

quote:
Originally posted by madhivanan

quote:
Originally posted by tomex1

quote:
Originally posted by madhivanan

You need to supply all the parameter values. Thats it

Madhivanan

Failing to plan is Planning to fail

Thanks madhivanan. But how does it identify which is going to wce_linkto table and which is going to wce_activity table?

You have already specified them in the INSERT statement

Madhivanan

Failing to plan is Planning to fail

We are nearly there. Everything seems to be working perfectly from SQL Server but when I try to execute it from our application, it still complains about the date format.

Basically we are trying to connect Microsoft Outlook to some tables in our SQL Serve. The application we are using to achieve this gives you the ability to run a stored procedure; which is why I built that procedure. The challenge I have got is that the application calles the procedure like this:

exec test2 @CREATETIME','@CREATEUSER','@DURATION','@SUBJECT','@UNIQUEID','@DURATION','@SUBJECT','@UNIQUEID','wce_contact','wce_history'

Now when I try that, I get this error message:

"Error converting data type varchar to datetime."

Any thoughts?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-28 : 09:14:36

What is the date value you passed to the parameter?

Madhivanan

Failing to plan is Planning to fail

tomex1
Starting Member

47 Posts

Posted - 2008-07-28 : 09:23:20

quote:
Originally posted by madhivanan

What is the date value you passed to the parameter?

Madhivanan

Failing to plan is Planning to fail

Ha, the date value should be Microsoft Outlook date and I presume it should be today's date. The actual column used in Outlook is called CreationDate and its datatype is 'datetime'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 10:08:07

quote:
Originally posted by tomex1

quote:
Originally posted by madhivanan

What is the date value you passed to the parameter?

Madhivanan

Failing to plan is Planning to fail

Ha, the date value should be Microsoft Outlook date and I presume it should be today's date. The actual column used in Outlook is called CreationDate and its datatype is 'datetime'

pass getdate() as value for date parameter. Also try setting the dateformat explicitly according to your format if you're getting conversion error as before.
SET DATEFORMAT for setting your reqd format

tomex1
Starting Member

47 Posts

Posted - 2008-07-28 : 10:15:59

quote:
Originally posted by visakh16

quote:
Originally posted by tomex1

quote:
Originally posted by madhivanan

What is the date value you passed to the parameter?

Madhivanan

Failing to plan is Planning to fail

Ha, the date value should be Microsoft Outlook date and I presume it should be today's date. The actual column used in Outlook is called CreationDate and its datatype is 'datetime'

pass getdate() as value for date parameter. Also try setting the dateformat explicitly according to your format if you're getting conversion error as before.
SET DATEFORMAT for setting your reqd format

How do I apply that to this?

exec test2 '@CreationDate', '@CREATEUSER','@DURATION','@SUBJECT','@UNIQUEID','wce_contact','wce_history'

The column in question is @CreationDate. Could you please show me an example of what you mean?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 10:20:39

quote:
Originally posted by tomex1

quote:
Originally posted by visakh16

quote:
Originally posted by tomex1

quote:
Originally posted by madhivanan

What is the date value you passed to the parameter?

Madhivanan

Failing to plan is Planning to fail

Ha, the date value should be Microsoft Outlook date and I presume it should be today's date. The actual column used in Outlook is called CreationDate and its datatype is 'datetime'

pass getdate() as value for date parameter. Also try setting the dateformat explicitly according to your format if you're getting conversion error as before.
SET DATEFORMAT for setting your reqd format

How do I apply that to this?

exec test2 '@CreationDate', '@CREATEUSER','@DURATION','@SUBJECT','@UNIQUEID','wce_contact','wce_history'

The column in question is @CreationDate. Could you please show me an example of what you mean?

why are passing the string values '@CreationDate', '@CREATEUSER',.. as values for sp parameters? are they variables? i think you probably meant this

exec test2  @CreationDate, @CREATEUSER,@DURATION,@SUBJECT,@UNIQUEID,'wce_contact','wce_history'

tomex1
Starting Member

47 Posts

Posted - 2008-07-28 : 11:26:12

quote:
Originally posted by visakh16

quote:
Originally posted by tomex1

quote:
Originally posted by visakh16

quote:
Originally posted by tomex1

quote:
Originally posted by madhivanan

What is the date value you passed to the parameter?

Madhivanan

Failing to plan is Planning to fail

Ha, the date value should be Microsoft Outlook date and I presume it should be today's date. The actual column used in Outlook is called CreationDate and its datatype is 'datetime'

pass getdate() as value for date parameter. Also try setting the dateformat explicitly according to your format if you're getting conversion error as before.
SET DATEFORMAT for setting your reqd format

How do I apply that to this?

exec test2 '@CreationDate', '@CREATEUSER','@DURATION','@SUBJECT','@UNIQUEID','wce_contact','wce_history'

The column in question is @CreationDate. Could you please show me an example of what you mean?

why are passing the string values '@CreationDate', '@CREATEUSER',.. as values for sp parameters? are they variables? i think you probably meant this

exec test2 @CreationDate, @CREATEUSER,@DURATION,@SUBJECT,@UNIQUEID,'wce_contact','wce_history'

I was wondering, do you know anything about Views? I am trying to create one and I keep getting this:
"Column names in each view or function must be unique. Column name 'CREATETIME' in view or function 'ContactsExample2' is specified more than once.
"

Thanks a lot for you help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 11:33:02

quote:
Originally posted by tomex1

quote:
Originally posted by visakh16

quote:
Originally posted by tomex1

quote:
Originally posted by visakh16

quote:
Originally posted by tomex1

quote:
Originally posted by madhivanan

What is the date value you passed to the parameter?

Madhivanan

Failing to plan is Planning to fail

Ha, the date value should be Microsoft Outlook date and I presume it should be today's date. The actual column used in Outlook is called CreationDate and its datatype is 'datetime'

pass getdate() as value for date parameter. Also try setting the dateformat explicitly according to your format if you're getting conversion error as before.
SET DATEFORMAT for setting your reqd format

How do I apply that to this?

exec test2 '@CreationDate', '@CREATEUSER','@DURATION','@SUBJECT','@UNIQUEID','wce_contact','wce_history'

The column in question is @CreationDate. Could you please show me an example of what you mean?

why are passing the string values '@CreationDate', '@CREATEUSER',.. as values for sp parameters? are they variables? i think you probably meant this

exec test2 @CreationDate, @CREATEUSER,@DURATION,@SUBJECT,@UNIQUEID,'wce_contact','wce_history'

I was wondering, do you know anything about Views? I am trying to create one and I keep getting this:
"Column names in each view or function must be unique. Column name 'CREATETIME' in view or function 'ContactsExample2' is specified more than once.
"

Thanks a lot for you help

Why not. A view is nothing more than a virtual table. i think above error is because you're using * in your select statements which joins onto more than 1 table and you've CREATETIME field coming from more than one table so that it causes duplicate column name. so either you should replace * with column names and exclude one of CREATETIME column or use a different name by aliasing for duplicated column.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-28 : 11:33:55

The error is very obvious

Madhivanan

Failing to plan is Planning to fail

tomex1
Starting Member

47 Posts

Posted - 2008-07-28 : 11:42:35