passing information

What i am trying to do is have the contact information to be displayed also. The contact information is in dbo.CONTSUPP under column 'contact'. Is there anyway to pass that 'contact' value up to the parent select statement. So the result will show Company, Address1,...,Source, contact.

SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source
FROM dbo.CONTACT1
WHERE dbo.CONTACT1.ACCOUNTNO IN (
SELECT ACCOUNTNO
FROM dbo.CONTSUPP
WHERE contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY ACCOUNTNO
HAVING COUNT(*) <= 1
)yup, derived table:

SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source
,t1.CONTSUPP
FROM dbo.CONTACT1 INNER JOIN
(
SELECT ACCOUNTNO, CONTSUPP
FROM dbo.CONTSUPP
WHERE contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY ACCOUNTNO
HAVING COUNT(*) <= 1
) As t1 ON t1.ACCOUNTNO = CONTACT1.ACCOUNTNO

You get the benefit of only generating the derived table once as well, as opposed to being evaluated once for each record when placed in the WHERE clause.|||Thank you, you pointed me in the right direction. There was one issue with the code you wrote because you cant group by ACCOUNTNO because the select has ACCOUNTNO and CONTACT. Anyway this is what the currently working code looks like. Thank you again, without your help I would not have been able to do this.

SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source,
t1.contact AS 'Device'
FROM dbo.CONTACT1
INNER JOIN (
SELECT accountno, contact
FROM dbo.CONTSUPP
WHERE accountno IN (
SELECT accountno
FROM dbo.CONTSUPP
WHERE contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY accountno
HAVING COUNT(*) <= 1
) AND (
contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
)
) AS t1
ON dbo.CONTACT1.accountno = t1.accountno

Passing an IN (a, b, c) list to a sproc as a string -- best method?

I want to do something like this in a stored proc:

--

Create Procedure dbo.GetPatients
@.PatientIdList varchar(200) -- comma separated list of PatientIDs
As

Select *
From Patients
Where PatientId In (@.PatientIdList)

--

I know the above won't work, but of course what I want is if
@.PatientIdList = '1,2,3' then I want Patient records with PatientIds
1, 2, and 3 returned.

It looks like the only way to do this is to build the SQL statement as
a string within the stored procedure ... which pretty much defeats the
usefulness of using precompiled sprocs as I understand it (better off
building a dynamic query against a View in that case).

Thoughts?

Joel Thornton ~ <groups@.joelpt.eml.cc>Joel,

Erland has a decent writing on this topic.

http://www.sommarskog.se/arrays-in-sql.html

--
-oj
http://www.rac4sql.net

"Joel Thornton" <joelpt@.eml.cc> wrote in message
news:c190a45a.0401072012.5c38ba06@.posting.google.c om...
> I want to do something like this in a stored proc:
> --
> Create Procedure dbo.GetPatients
> @.PatientIdList varchar(200) -- comma separated list of PatientIDs
> As
> Select *
> From Patients
> Where PatientId In (@.PatientIdList)
> --
> I know the above won't work, but of course what I want is if
> @.PatientIdList = '1,2,3' then I want Patient records with PatientIds
> 1, 2, and 3 returned.
> It looks like the only way to do this is to build the SQL statement as
> a string within the stored procedure ... which pretty much defeats the
> usefulness of using precompiled sprocs as I understand it (better off
> building a dynamic query against a View in that case).
>
> Thoughts?
> Joel Thornton ~ <groups@.joelpt.eml.ccsql

Passing an Array to a Stored Procedures

How to do this ?

==============================
CREATE procedure dbo.AddTb2FromTb1
@.Tb1No nvarchar(1000)
as
insert into Tb2 (*)
select * from Tb1
where Tb1 IN (@.Tb1No) /* How to Passing an Array to a Stored Procedures ? */
==============================

dbo.AddTb2FromTb1 'No001' is Work !
dbo.AddTb2FromTb1 'No001,No002,Bo003' is not Work !If you are using SQL 2000 you can create a user-defined fnction - have a look at the following link:

Sql Team|||I Try CnvToChar() is work...

select * from CsvToChar('PO001234,PO123') is work

CharValue
-----
PO001234
Po123
-----

but,

=====================================================
select * from Tb1 Where TbNo IN (select * from CsvToChar('PO001234,PO123'))
have error message:

Server: Message 446, Level 16, Status 9, Line 1
can not to analyze 'equal to' action order collide.
=====================================================

thank you help...|||My SQL Server is Chinese System,
Character Record Fields in Table is default 'COLLATE Chinese_Taiwan_Stroke_BIN' vaule,
so i add the Default value in the function CvsTochar() return value.

==============================================
CREATE Function dbo.CsvToChar ( @.Array varchar(1000))
returns @.CharTable table
(CharValue char(10) COLLATE Chinese_Taiwan_Stroke_BIN)
AS
begin

declare @.separator char(1)
set @.separator = ','
.........
.........
===============================================

No error message to display, It's work... ^_^ Y

Ehorn - Thank you very much !!!

Johnny SCB

Passing a subquery as a parameter to a user defined function

Try the following:
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
NS_REPORT_SAVE where savereportid = 8)',',')
Chris wrote:
> I have a function which accepts a string as a parameter and returns a tabl
e.
> It is a bit like a split function. It works when I pass the string as a
> variable. When I try to pass in the string variable as the result of a
> subquery I get an error.
> This works
> declare @.test varchar(50)
> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
> This doesn't
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
> NS_REPORT_SAVE where savereportid = 8),',')
> I get
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '('.
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.The value that is passed as the first parameter is a comma separted field
e.g.'1,34,23' so it is expecting something in that format. That particular
subquery returns an appropriate value. Is the subquery seen as a table and
you can't pass a table to a subquery?
<bharat.gidwani@.gmail.com> wrote in message
news:1151339095.892515.168280@.r2g2000cwb.googlegroups.com...
> Try the following:
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
> NS_REPORT_SAVE where savereportid = 8)',',')
> Chris wrote:
>|||I have a function which accepts a string as a parameter and returns a table.
It is a bit like a split function. It works when I pass the string as a
variable. When I try to pass in the string variable as the result of a
subquery I get an error.
This works
declare @.test varchar(50)
set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
This doesn't
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
NS_REPORT_SAVE where savereportid = 8),',')
I get
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.|||Try the following:
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
NS_REPORT_SAVE where savereportid = 8)',',')
Chris wrote:
> I have a function which accepts a string as a parameter and returns a tabl
e.
> It is a bit like a split function. It works when I pass the string as a
> variable. When I try to pass in the string variable as the result of a
> subquery I get an error.
> This works
> declare @.test varchar(50)
> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
> This doesn't
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
> NS_REPORT_SAVE where savereportid = 8),',')
> I get
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '('.
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.|||The value that is passed as the first parameter is a comma separted field
e.g.'1,34,23' so it is expecting something in that format. That particular
subquery returns an appropriate value. Is the subquery seen as a table and
you can't pass a table to a subquery?
<bharat.gidwani@.gmail.com> wrote in message
news:1151339095.892515.168280@.r2g2000cwb.googlegroups.com...
> Try the following:
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
> NS_REPORT_SAVE where savereportid = 8)',',')
> Chris wrote:
>

Pass datetime as parameter to SP

my SP like this
CREATE PROCEDURE dbo.sel_apinv_nonsettle
@.cocode varchar(10),
@.billcode varchar(10),
@.issuedate datetime,
@.branchid varchar(10),
@.accttype varchar(10)
IN SQL Analyzer,
exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
and i got this error "Server: Msg 8114, Level 16, State 4, Procedure
sel_apinv_nonsettle, Line 0
Error converting data type nvarchar to datetime."
What's wrong 'Hi
Probably you need to put it this way
IN SQL Analyzer,
exec sel_apinv_nonsettle 'CNC','CNC',getdate(),'HLSHK','APINV'
getdate() is a function
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"Agnes" wrote:

> my SP like this
> CREATE PROCEDURE dbo.sel_apinv_nonsettle
> @.cocode varchar(10),
> @.billcode varchar(10),
> @.issuedate datetime,
> @.branchid varchar(10),
> @.accttype varchar(10)
> IN SQL Analyzer,
> exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
> and i got this error "Server: Msg 8114, Level 16, State 4, Procedure
> sel_apinv_nonsettle, Line 0
> Error converting data type nvarchar to datetime."
> What's wrong '
>
>|||Stored Proc Parameters can only be constants, or T-SQL Variables... You cnno
t
directly pass an expression, or a function, to a stored proc... And even if
you could, the function getdate would require the open closed parenethses at
the end -- getdate()...
What you need to do is create a T-SQL Variable, set it to be equal to
getdate(), and then pass that variable.
Declare @.GDT DateTime
Set @.GDT = getdate() -- ("Current_TimeStamp" is ANSI-SQL Standard)
exec sel_apinv_nonsettle 'CNC','CNC',@.GDT,'HLSHK','APINV'
exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
"Agnes" wrote:

> my SP like this
> CREATE PROCEDURE dbo.sel_apinv_nonsettle
> @.cocode varchar(10),
> @.billcode varchar(10),
> @.issuedate datetime,
> @.branchid varchar(10),
> @.accttype varchar(10)
> IN SQL Analyzer,
> exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
> and i got this error "Server: Msg 8114, Level 16, State 4, Procedure
> sel_apinv_nonsettle, Line 0
> Error converting data type nvarchar to datetime."
> What's wrong '
>
>