stored procedure not sure where to start

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

stored procedure not sure where to start

Author

Topic

katiev
Starting Member

18 Posts

Posted - 2008-11-03 : 11:42:39

I am very new to SQL and trying to write a stored procedure for a linked server that basically contains a select statement
This is the select statement:

'SELECT DISTINCT CSRV_SALESMAN.CSSLSMN_NAME, SCALE_CORP.SCLC_NAME, CSRV_ORDER_HDR.ORDHD_ORDER, CSRV_ORDER_HDR.PK_ID, CSRV_ORDER_HDR.ORDHD_CORP, CSRV_ORDER_LINE.ORDLN_PLANT, CSRV_ORDER_LINE.ORDLN_LINE_DESC, CSRV_ORDER_PRICE.ORDPRC_EFF_DATE, CSRV_ORDER_LINE.ORDLN_MAJOR, CSRV_ORDER_LINE.ORDLN_MINOR, CSRV_ORDER_PRICE.ORDPRC_PLT_PRICE, CSRV_ORDER_PRICE.ORDPRC_DEL_PRICE, CSRV_ORDER_HDR.ORDHD_ADD_DATE, CSRV_ORDER_HDR.ORDHD_JOB_NAME, CSRV_ORDER_HDR.ORDHD_JOB_LOCATE, CSRV_ORDER_HDR.ORDHD_JOB_ROUTE, CSRV_ORDER_HDR.ORDHD_EXP_DATE, CSRV_ORDER_HDR.ORDHD_PLANT, CSRV_ORDER_LINE.ORDLN_QTY, CSRV_ORDER_LINE.ORDLN_HAUL_PAY_UM, CSRV_ORDER_HDR.ORDHD_CUSTOMER, CSRV_CUST_CHILD.CUSTCLD_EMAIL, CSRV_ORDER_LINE.ORDLN_PRINT_PRICE, CSRV_ADDRESSES.CSRVADD_STREET2, CSRV_ADDRESSES.CSRVADD_NAME, CSRV_ADDRESSES.CSRVADD_STREET, CSRV_ADDRESSES.CSRVADD_CITY, CSRV_ADDRESSES.CSRVADD_STATE, CSRV_ADDRESSES.CSRVADD_ZIP, CSRV_ORDER_HDR.ORDHD_QUOTE
FROM (((((DBADM.CSRV_ORDER_HDR CSRV_ORDER_HDR INNER JOIN DBADM.CSRV_ORDER_LINE CSRV_ORDER_LINE ON (CSRV_ORDER_HDR.PK_ID=CSRV_ORDER_LINE.FK_CSORDHD_ID) AND (CSRV_ORDER_HDR.ORDHD_CORP=CSRV_ORDER_LINE.ORDLN_CORP)) LEFT OUTER JOIN DBADM.CSRV_ADDRESSES CSRV_ADDRESSES ON (((CSRV_ORDER_HDR.ORDHD_CORP=CSRV_ADDRESSES.CSRVADD_CORP) AND (CSRV_ORDER_HDR.ORDHD_CUSTOMER=CSRV_ADDRESSES.CSRVADD_CUSTOMER)) AND (CSRV_ORDER_HDR.ORDHD_SUFFIX=CSRV_ADDRESSES.CSRVADD_SUFFIX)) AND (CSRV_ORDER_HDR.ORDHD_COMPANY=CSRV_ADDRESSES.CSRVADD_COMPANY)) LEFT OUTER JOIN DBADM.CSRV_CUST_CHILD CSRV_CUST_CHILD ON (((CSRV_ORDER_HDR.ORDHD_CORP=CSRV_CUST_CHILD.CUSTCLD_CORP) AND (CSRV_ORDER_HDR.ORDHD_CUSTOMER=CSRV_CUST_CHILD.CUSTCLD_CUSTOMER)) AND (CSRV_ORDER_HDR.ORDHD_SUFFIX=CSRV_CUST_CHILD.CUSTCLD_SUFFIX)) AND (CSRV_ORDER_HDR.ORDHD_COMPANY=CSRV_CUST_CHILD.CUSTCLD_COMPANY)) LEFT OUTER JOIN DBADM.CSRV_ORDER_PRICE CSRV_ORDER_PRICE ON (CSRV_ORDER_LINE.PK_ID=CSRV_ORDER_PRICE.FK_CSORLINE_ID) AND (CSRV_ORDER_LINE.ORDLN_CORP=CSRV_ORDER_PRICE.ORDPRC_CORP)) LEFT OUTER JOIN DBADM.CSRV_SALESMAN CSRV_SALESMAN ON (CSRV_ORDER_LINE.ORDLN_CORP=CSRV_SALESMAN.CSSLSMN_CORP) AND (CSRV_ORDER_LINE.ORDLN_SALESMAN=CSRV_SALESMAN.CSSLSMN_ID)) LEFT OUTER JOIN DBADM.SCALE_CORP SCALE_CORP ON (CSRV_ORDER_LINE.ORDLN_CORP=SCALE_CORP.SCLC_CORP) AND (CSRV_ORDER_LINE.ORDLN_PLANT=SCALE_CORP.SCLC_PLANT)
WHERE CSRV_ORDER_HDR.PK_ID=
ORDER BY CSRV_ORDER_HDR.ORDHD_CORP, CSRV_ORDER_HDR.ORDHD_ORDER, CSRV_ORDER_LINE.ORDLN_PLANT, CSRV_ORDER_LINE.ORDLN_LINE_DESC'

and I don't know where to start....

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 11:46:51

have you configured the linked server? also have you tried the above query and is it working fine? is your question just to create procedure out of the above query?

katiev
Starting Member

18 Posts

Posted - 2008-11-03 : 11:50:56

Yes, the linked server is created named: db2_cs
and yes, the query works fine as long as i have a hard coded pkid after the where clause : WHERE CSRV_ORDER_HDR.PK_ID=

So, yes, i guess my question is how do I create a stored procedure from a query, or this query?

katiev
Starting Member

18 Posts

Posted - 2008-11-03 : 11:52:50

I got this one to work, but need it to be more specific

USE [DB2_HIS]
GO
/****** Object: StoredProcedure [dbo].[SP_SQL] Script Date: 11/03/2008 11:51:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_SQL]
@TSQL varchar(3000)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @OPENQUERY nvarchar(50), @LinkedServer nvarchar(10), @Closing nvarchar(5)
SET @LinkedServer = '[DB2_CS]'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @Closing = ''')'
EXEC (@OPENQUERY+@TSQL+@Closing)
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 11:57:27

what do you mean by more specific?

katiev
Starting Member

18 Posts

Posted - 2008-11-03 : 12:35:12

well, the one above, from the way i understand that I wrote it... and again, i am very new to this and feeling 'lucky' when it actually works but it is what my boss and I call the wildcard one, pretty much pulling any select statement that we want after the TSL parameter, and the one i need to get to work is the specific select statement that I posted with the pk_id number being the parameter...

does that seem right the way i explained it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 12:38:31

cant you just replace @TSQL with your select statement then?

katiev
Starting Member

18 Posts

Posted - 2008-11-03 : 12:41:36

yes, that would make sense i guess but, if I do that, then how would I put in the parameter for the PK_ID that I actually need to prompt for?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 12:45:45

Add a parameter PK_ID to procedure to pass the value.

katiev
Starting Member

18 Posts

Posted - 2008-11-03 : 12:46:17

as you can tell, im lost.... so, you're saying, code it like this:

USE [DB2_HIS]
GO
/****** Object: StoredProcedure [dbo].[SP_SQL] Script Date: 11/03/2008 11:51:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[SP_orderprint]
Declare @TSQL = 'SELECT DISTINCT CSRV_SALESMAN.CSSLSMN_NAME, SCALE_CORP.SCLC_NAME, CSRV_ORDER_HDR.ORDHD_ORDER, CSRV_ORDER_HDR.PK_ID, CSRV_ORDER_HDR.ORDHD_CORP, CSRV_ORDER_LINE.ORDLN_PLANT, CSRV_ORDER_LINE.ORDLN_LINE_DESC, CSRV_ORDER_PRICE.ORDPRC_EFF_DATE, CSRV_ORDER_LINE.ORDLN_MAJOR, CSRV_ORDER_LINE.ORDLN_MINOR, CSRV_ORDER_PRICE.ORDPRC_PLT_PRICE, CSRV_ORDER_PRICE.ORDPRC_DEL_PRICE, CSRV_ORDER_HDR.ORDHD_ADD_DATE, CSRV_ORDER_HDR.ORDHD_JOB_NAME, CSRV_ORDER_HDR.ORDHD_JOB_LOCATE, CSRV_ORDER_HDR.ORDHD_JOB_ROUTE, CSRV_ORDER_HDR.ORDHD_EXP_DATE, CSRV_ORDER_HDR.ORDHD_PLANT, CSRV_ORDER_LINE.ORDLN_QTY, CSRV_ORDER_LINE.ORDLN_HAUL_PAY_UM, CSRV_ORDER_HDR.ORDHD_CUSTOMER, CSRV_CUST_CHILD.CUSTCLD_EMAIL, CSRV_ORDER_LINE.ORDLN_PRINT_PRICE, CSRV_ADDRESSES.CSRVADD_STREET2, CSRV_ADDRESSES.CSRVADD_NAME, CSRV_ADDRESSES.CSRVADD_STREET, CSRV_ADDRESSES.CSRVADD_CITY, CSRV_ADDRESSES.CSRVADD_STATE, CSRV_ADDRESSES.CSRVADD_ZIP, CSRV_ORDER_HDR.ORDHD_QUOTE
FROM (((((DBADM.CSRV_ORDER_HDR CSRV_ORDER_HDR INNER JOIN DBADM.CSRV_ORDER_LINE CSRV_ORDER_LINE ON (CSRV_ORDER_HDR.PK_ID=CSRV_ORDER_LINE.FK_CSORDHD_ID) AND (CSRV_ORDER_HDR.ORDHD_CORP=CSRV_ORDER_LINE.ORDLN_CORP)) LEFT OUTER JOIN DBADM.CSRV_ADDRESSES CSRV_ADDRESSES ON (((CSRV_ORDER_HDR.ORDHD_CORP=CSRV_ADDRESSES.CSRVADD_CORP) AND (CSRV_ORDER_HDR.ORDHD_CUSTOMER=CSRV_ADDRESSES.CSRVADD_CUSTOMER)) AND (CSRV_ORDER_HDR.ORDHD_SUFFIX=CSRV_ADDRESSES.CSRVADD_SUFFIX)) AND (CSRV_ORDER_HDR.ORDHD_COMPANY=CSRV_ADDRESSES.CSRVADD_COMPANY)) LEFT OUTER JOIN DBADM.CSRV_CUST_CHILD CSRV_CUST_CHILD ON (((CSRV_ORDER_HDR.ORDHD_CORP=CSRV_CUST_CHILD.CUSTCLD_CORP) AND (CSRV_ORDER_HDR.ORDHD_CUSTOMER=CSRV_CUST_CHILD.CUSTCLD_CUSTOMER)) AND (CSRV_ORDER_HDR.ORDHD_SUFFIX=CSRV_CUST_CHILD.CUSTCLD_SUFFIX)) AND (CSRV_ORDER_HDR.ORDHD_COMPANY=CSRV_CUST_CHILD.CUSTCLD_COMPANY)) LEFT OUTER JOIN DBADM.CSRV_ORDER_PRICE CSRV_ORDER_PRICE ON (CSRV_ORDER_LINE.PK_ID=CSRV_ORDER_PRICE.FK_CSORLINE_ID) AND (CSRV_ORDER_LINE.ORDLN_CORP=CSRV_ORDER_PRICE.ORDPRC_CORP)) LEFT OUTER JOIN DBADM.CSRV_SALESMAN CSRV_SALESMAN ON (CSRV_ORDER_LINE.ORDLN_CORP=CSRV_SALESMAN.CSSLSMN_CORP) AND (CSRV_ORDER_LINE.ORDLN_SALESMAN=CSRV_SALESMAN.CSSLSMN_ID)) LEFT OUTER JOIN DBADM.SCALE_CORP SCALE_CORP ON (CSRV_ORDER_LINE.ORDLN_CORP=SCALE_CORP.SCLC_CORP) AND (CSRV_ORDER_LINE.ORDLN_PLANT=SCALE_CORP.SCLC_PLANT)
WHERE CSRV_ORDER_HDR.PK_ID=
ORDER BY CSRV_ORDER_HDR.ORDHD_CORP, CSRV_ORDER_HDR.ORDHD_ORDER, CSRV_ORDER_LINE.ORDLN_PLANT, CSRV_ORDER_LINE.ORDLN_LINE_DESC'
@PK_ID varchar (7)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @OPENQUERY nvarchar(50), @LinkedServer nvarchar(10), @Closing nvarchar(5)
SET @LinkedServer = '[DB2_CS]'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @Closing = ''')'
EXEC (@OPENQUERY+@TSQL+@Closing)
END

im completely lost...

katiev
Starting Member

18 Posts

Posted - 2008-11-03 : 12:48:16

one other question... is there another way to write it without replacing my @tsql with the select statement, or is that the best way to do it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 12:54:17

i meant this

USE [DB2_HIS]
GO
/****** Object: StoredProcedure [dbo].[SP_SQL] Script Date: 11/03/2008 11:51:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[SP_orderprint]
@PK_ID varchar (7)
AS

BEGIN
SET NOCOUNT ON;

SELECT * FROM OPENQUERY([DB2_CS],'SELECT DISTINCT CSRV_SALESMAN.CSSLSMN_NAME, SCALE_CORP.SCLC_NAME, CSRV_ORDER_HDR.ORDHD_ORDER, CSRV_ORDER_HDR.PK_ID, CSRV_ORDER_HDR.ORDHD_CORP, CSRV_ORDER_LINE.ORDLN_PLANT, CSRV_ORDER_LINE.ORDLN_LINE_DESC, CSRV_ORDER_PRICE.ORDPRC_EFF_DATE, CSRV_ORDER_LINE.ORDLN_MAJOR, CSRV_ORDER_LINE.ORDLN_MINOR, CSRV_ORDER_PRICE.ORDPRC_PLT_PRICE, CSRV_ORDER_PRICE.ORDPRC_DEL_PRICE, CSRV_ORDER_HDR.ORDHD_ADD_DATE, CSRV_ORDER_HDR.ORDHD_JOB_NAME, CSRV_ORDER_HDR.ORDHD_JOB_LOCATE, CSRV_ORDER_HDR.ORDHD_JOB_ROUTE, CSRV_ORDER_HDR.ORDHD_EXP_DATE, CSRV_ORDER_HDR.ORDHD_PLANT, CSRV_ORDER_LINE.ORDLN_QTY, CSRV_ORDER_LINE.ORDLN_HAUL_PAY_UM, CSRV_ORDER_HDR.ORDHD_CUSTOMER, CSRV_CUST_CHILD.CUSTCLD_EMAIL, CSRV_ORDER_LINE.ORDLN_PRINT_PRICE, CSRV_ADDRESSES.CSRVADD_STREET2, CSRV_ADDRESSES.CSRVADD_NAME, CSRV_ADDRESSES.CSRVADD_STREET, CSRV_ADDRESSES.CSRVADD_CITY, CSRV_ADDRESSES.CSRVADD_STATE, CSRV_ADDRESSES.CSRVADD_ZIP, CSRV_ORDER_HDR.ORDHD_QUOTE
FROM (((((DBADM.CSRV_ORDER_HDR CSRV_ORDER_HDR INNER JOIN DBADM.CSRV_ORDER_LINE CSRV_ORDER_LINE ON (CSRV_ORDER_HDR.PK_ID=CSRV_ORDER_LINE.FK_CSORDHD_ID) AND (CSRV_ORDER_HDR.ORDHD_CORP=CSRV_ORDER_LINE.ORDLN_CORP)) LEFT OUTER JOIN DBADM.CSRV_ADDRESSES CSRV_ADDRESSES ON (((CSRV_ORDER_HDR.ORDHD_CORP=CSRV_ADDRESSES.CSRVADD_CORP) AND (CSRV_ORDER_HDR.ORDHD_CUSTOMER=CSRV_ADDRESSES.CSRVADD_CUSTOMER)) AND (CSRV_ORDER_HDR.ORDHD_SUFFIX=CSRV_ADDRESSES.CSRVADD_SUFFIX)) AND (CSRV_ORDER_HDR.ORDHD_COMPANY=CSRV_ADDRESSES.CSRVADD_COMPANY)) LEFT OUTER JOIN DBADM.CSRV_CUST_CHILD CSRV_CUST_CHILD ON (((CSRV_ORDER_HDR.ORDHD_CORP=CSRV_CUST_CHILD.CUSTCLD_CORP) AND (CSRV_ORDER_HDR.ORDHD_CUSTOMER=CSRV_CUST_CHILD.CUSTCLD_CUSTOMER)) AND (CSRV_ORDER_HDR.ORDHD_SUFFIX=CSRV_CUST_CHILD.CUSTCLD_SUFFIX)) AND (CSRV_ORDER_HDR.ORDHD_COMPANY=CSRV_CUST_CHILD.CUSTCLD_COMPANY)) LEFT OUTER JOIN DBADM.CSRV_ORDER_PRICE CSRV_ORDER_PRICE ON (CSRV_ORDER_LINE.PK_ID=CSRV_ORDER_PRICE.FK_CSORLINE_ID) AND (CSRV_ORDER_LINE.ORDLN_CORP=CSRV_ORDER_PRICE.ORDPRC_CORP)) LEFT OUTER JOIN DBADM.CSRV_SALESMAN CSRV_SALESMAN ON (CSRV_ORDER_LINE.ORDLN_CORP=CSRV_SALESMAN.CSSLSMN_CORP) AND (CSRV_ORDER_LINE.ORDLN_SALESMAN=CSRV_SALESMAN.CSSLSMN_ID)) LEFT OUTER JOIN DBADM.SCALE_CORP SCALE_CORP ON (CSRV_ORDER_LINE.ORDLN_CORP=SCALE_CORP.SCLC_CORP) AND (CSRV_ORDER_LINE.ORDLN_PLANT=SCALE_CORP.SCLC_PLANT)
WHERE CSRV_ORDER_HDR.PK_ID=@PK_ID
ORDER BY CSRV_ORDER_HDR.ORDHD_CORP, CSRV_ORDER_HDR.ORDHD_ORDER, CSRV_ORDER_LINE.ORDLN_PLANT, CSRV_ORDER_LINE.ORDLN_LINE_DESC')
END
GO

katiev
Starting Member

18 Posts

Posted - 2008-11-03 : 13:03:24

THANK YOU THANK YOU THANK YOU! It worked that way and I see now how it really is just like a sentence...

But difficult none the less for me!

Now onto my next task, getting this working as a datasource in reporting services... have a great day and thanks again!

Just so you know what I did, the whole thing ended up being this and it worked and pulled data...
USE [DB2_HIS]
GO
/****** Object: StoredProcedure [dbo].[SP_ORDERPRINT] Script Date: 11/03/2008 12:59:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[SP_ORDERPRINT]
@PK_ID varchar(7)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @OPENQUERY nvarchar(50), @LinkedServer nvarchar(10), @Closing nvarchar(5), @TSQL nvarchar (3000)
SET @LinkedServer = '[DB2_CS]'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT DISTINCT CSRV_SALESMAN.CSSLSMN_NAME, SCALE_CORP.SCLC_NAME, CSRV_ORDER_HDR.ORDHD_ORDER, CSRV_ORDER_HDR.PK_ID, CSRV_ORDER_HDR.ORDHD_CORP, CSRV_ORDER_LINE.ORDLN_PLANT, CSRV_ORDER_LINE.ORDLN_LINE_DESC, CSRV_ORDER_PRICE.ORDPRC_EFF_DATE, CSRV_ORDER_LINE.ORDLN_MAJOR, CSRV_ORDER_LINE.ORDLN_MINOR, CSRV_ORDER_PRICE.ORDPRC_PLT_PRICE, CSRV_ORDER_PRICE.ORDPRC_DEL_PRICE, CSRV_ORDER_HDR.ORDHD_ADD_DATE, CSRV_ORDER_HDR.ORDHD_JOB_NAME, CSRV_ORDER_HDR.ORDHD_JOB_LOCATE, CSRV_ORDER_HDR.ORDHD_JOB_ROUTE, CSRV_ORDER_HDR.ORDHD_EXP_DATE, CSRV_ORDER_HDR.ORDHD_PLANT, CSRV_ORDER_LINE.ORDLN_QTY, CSRV_ORDER_LINE.ORDLN_HAUL_PAY_UM, CSRV_ORDER_HDR.ORDHD_CUSTOMER, CSRV_CUST_CHILD.CUSTCLD_EMAIL, CSRV_ORDER_LINE.ORDLN_PRINT_PRICE, CSRV_ADDRESSES.CSRVADD_STREET2, CSRV_ADDRESSES.CSRVADD_NAME, CSRV_ADDRESSES.CSRVADD_STREET, CSRV_ADDRESSES.CSRVADD_CITY, CSRV_ADDRESSES.CSRVADD_STATE, CSRV_ADDRESSES.CSRVADD_ZIP, CSRV_ORDER_HDR.ORDHD_QUOTE
FROM (((((DBADM.CSRV_ORDER_HDR CSRV_ORDER_HDR INNER JOIN DBADM.CSRV_ORDER_LINE CSRV_ORDER_LINE ON (CSRV_ORDER_HDR.PK_ID=CSRV_ORDER_LINE.FK_CSORDHD_ID) AND (CSRV_ORDER_HDR.ORDHD_CORP=CSRV_ORDER_LINE.ORDLN_CORP)) LEFT OUTER JOIN DBADM.CSRV_ADDRESSES CSRV_ADDRESSES ON (((CSRV_ORDER_HDR.ORDHD_CORP=CSRV_ADDRESSES.CSRVADD_CORP) AND (CSRV_ORDER_HDR.ORDHD_CUSTOMER=CSRV_ADDRESSES.CSRVADD_CUSTOMER)) AND (CSRV_ORDER_HDR.ORDHD_SUFFIX=CSRV_ADDRESSES.CSRVADD_SUFFIX)) AND (CSRV_ORDER_HDR.ORDHD_COMPANY=CSRV_ADDRESSES.CSRVADD_COMPANY)) LEFT OUTER JOIN DBADM.CSRV_CUST_CHILD CSRV_CUST_CHILD ON (((CSRV_ORDER_HDR.ORDHD_CORP=CSRV_CUST_CHILD.CUSTCLD_CORP) AND (CSRV_ORDER_HDR.ORDHD_CUSTOMER=CSRV_CUST_CHILD.CUSTCLD_CUSTOMER)) AND (CSRV_ORDER_HDR.ORDHD_SUFFIX=CSRV_CUST_CHILD.CUSTCLD_SUFFIX)) AND (CSRV_ORDER_HDR.ORDHD_COMPANY=CSRV_CUST_CHILD.CUSTCLD_COMPANY)) LEFT OUTER JOIN DBADM.CSRV_ORDER_PRICE CSRV_ORDER_PRICE ON (CSRV_ORDER_LINE.PK_ID=CSRV_ORDER_PRICE.FK_CSORLINE_ID) AND (CSRV_ORDER_LINE.ORDLN_CORP=CSRV_ORDER_PRICE.ORDPRC_CORP)) LEFT OUTER JOIN DBADM.CSRV_SALESMAN CSRV_SALESMAN ON (CSRV_ORDER_LINE.ORDLN_CORP=CSRV_SALESMAN.CSSLSMN_CORP) AND (CSRV_ORDER_LINE.ORDLN_SALESMAN=CSRV_SALESMAN.CSSLSMN_ID)) LEFT OUTER JOIN DBADM.SCALE_CORP SCALE_CORP ON (CSRV_ORDER_LINE.ORDLN_CORP=SCALE_CORP.SCLC_CORP) AND (CSRV_ORDER_LINE.ORDLN_PLANT=SCALE_CORP.SCLC_PLANT)
WHERE CSRV_ORDER_HDR.PK_ID=' + @PK_ID + '
ORDER BY CSRV_ORDER_HDR.ORDHD_CORP, CSRV_ORDER_HDR.ORDHD_ORDER, CSRV_ORDER_LINE.ORDLN_PLANT, CSRV_ORDER_LINE.ORDLN_LINE_DESC'
SET @Closing = ''')'
EXEC (@OPENQUERY+@TSQL+@Closing)
END

THANKS AGAIN!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 13:07:41

Cheers

let me know if you had any difficulty in setting up datasource. bye 4 today

katiev
Starting Member

18 Posts

Posted - 2008-11-03 : 13:09:45

Im sure I will... im going to set up 3 more stored procs for now and then I am sure I will have questions on how to set up the datasource... this is all new to me, go figure, i got a job as a net admin and im doing all this... ha ha

Thanks again!

katiev
Starting Member

18 Posts

Posted - 2008-11-03 : 13:31:50

you still around? I gave up on the other stored procedures at this point, cause my boss needs this report to work...

first I have a test report that i need to set up the datasource for... I am going into my shared data sources and creating a datasourse pointing to my sql server, using stored proc, and calling the stored proc i just created, when i execute this in query designer, it prompts for the pk_id, like i want it to and it pulls data, i go to save this as my dataset and I get the following: ms report designer could not create a list for teh query verify that you can connect to the data source and that your query syntax is correct....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources