Passing in variable number of parameters to a stored procedure

I am fairly new to MSSQL. Looking for a answer to a simple question.

I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.

The structure of this table is something like

PKID
customerID
email address
....

customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...

Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?

Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.

Any other ways to do this? ThanksFrom a relational perspective, the best answer is to write a single stored procedure that takes one email address and processes it, and then call that procedure N times from your UI. This is because a relational database is based on relational algebra, and while that processes sets well as output, it doesn't process them nearly as easily as input.

If you decide to pursue your original idea and use a delimited (probably comma separated) list, you can use fSplit (http://www.dbforums.com/t997070.html) which I posted here ages ago. This is cleaner from the UI perspective, but it will eventually byte you because of the poor fit with relational databases.

-PatP|||This is because a relational database is based on relational algebra, and while that processes sets well as output, it doesn't process them nearly as easily as input.Really? I never knew that.

What's the basic reasoning around that Pat?|||Ok - I had a few mins. Played around.

CREATE TABLE InsertTable
(
MyPKFld VarChar(5) PRIMARY KEY
)

DECLARE @.i AS SmallInt

SET NOCOUNT ON

DECLARE @.Insert AS VarChar(2000)

--SELECT @.Insert = '10001,10022,20099,15073,28948,18737,90273,27910,3 7891'
SELECT @.Insert = '10001,10022,15073,18737,20099,27910,28948,37891,9 0273'

SELECT @.i = 1

DECLARE @.LoopUpper AS TinyInt

SELECT @.LoopUpper = (SELECT COUNT(*) FROM dbo.Split(@.Insert, ','))

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

DECLARE @.Start AS DateTime

SELECT @.Start = GETDATE()

WHILE @.i <= @.LoopUpper BEGIN

INSERT INTO InsertTable
SELECT Data
FROM dbo.Split(@.Insert, ',')
WHERE ID = @.i
SELECT @.i = @.i + 1

END

PRINT 'LOOP takes ' + CAST(DATEDIFF(ms, @.Start, GETDATE()) AS VarChar(4)) + 'ms'

DELETE
FROM InsertTable
WHERE MyPKFld > 10000

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SELECT @.Start = GETDATE()

INSERT INTO InsertTable
SELECT Data
FROM dbo.Split(@.Insert, ',')
PRINT 'SET takes ' + CAST(DATEDIFF(ms, @.Start, GETDATE()) AS VarChar(4)) + 'ms'

DROP TABLE InsertTable
Basically tests looping and inserting one record on each pass and inserting a set. I typically get the set at between 1/3 and 2/3 the time the loop takes. What have I missed?|||What's the basic reasoning around that Pat?In a set based environment (where sets are fully supported by both the language itself and the implementation), there's no issue. SQL as a language doesn't support passing sets in (at least it doesn't yet, the new draft standard has basic support for them).

What you are doing is passing a complex argument (more than one atomic element in a single argument). That is outside of relational algebra altogether since it violates first normal form. The reason it runs faster is that you're trading multiple calls in a relational solution for processing complexity in a code oriented solution. It certainly works, and at least for now it is faster, but eventually it will get to the point that it causes problems.

-PatP|||Thanks Pat
I don't think I totally get you. The csv string is not normalised. However the function (or whatever code one might run) normalises the input. As far as SQL Server is concerned, it might always have been a set.

It certainly works, and at least for now it is faster, but eventually it will get to the point that it causes problems.By this do you mean it will be a bugger to maintain or do you mean there will be some sort of technical problem over time? If the latter - what would that be?

Grateful for the education as ever :)|||When you create a non-normalized interface like this, you've broken one of the fundamental building block "contracts" between a client and server. That relationship is either relational, or it isn't relational, and any non-relational interface makes the relationship between client and server non-relational.

This kind of change can make sense when you're implementing a different paradyme such as OOP. When you do that, you leave the relational world behind, so the rigorous "proofs" of behavior no longer have any meaning, but that happens any time you switch from one paradyme to another.

There are lots of really fundamental characteristics involved in relational processing. These make it predictable, which in turn makes it dependable. While relational technology certainly isn't the best possible way to do things, it is the best that I've found so far that is widely commercially supported and clearly understood by many professionals.

There are thousands of ways this can go wrong (and I've personally tried several hundred of those ;)). One example would be that you could have an application start out as a single server implementation, grow to use a cluster, expand further to use a cloud of replicating servers... When something goes worng :o in the process of getting data from a web server client into the data cloud, you have to start using network monitors to find the problem since you can't rely on which app/web server will initiate the conversation and which database server will process it. If you have bundled multiple calls into one and then rely on the server to parse them, you can no longer predict what the data "payload" will be exactly, so you need to start doing moderately sophisticated pattern matching. The process gets ugly, really fast.

Not everyone will face this specific problem. Given sufficient time though, I'll guarantee that you'll hit some problem related to the bundling effect. If you are making a paradyme shift, and that shift buys you something substantial in terms of coding time, support, ability to use new features, etc. then it is certainly worth considering. If all it buys you is a slight performance gain in exchange for the predictability of the pure relational model, I'd be hard pressed to "green light" this change.

-PatP|||Thanks Pat - appreciated :D

As it happens - I asked this question some time ago and got one yay and one nay from two of your esteemed peers hence why I jumped on your answer.
EDIT - plus my initial reading of your answer went against everything I thought I knew about SQL.sql

Passing an multi array to sql from java over jdbc

Hi Guys,

I am fairly new to PL and I am trying to solve this problem.

I need to pass a two dimensional array(A variable Name and Variable value) to a stored procedure in PL/SQL. I dont want to pass this data as a big string..I was hoping to passing it as an array because it would be easier to handle the data.

Basically I would be calling this procedure from a java program over JDBC.

My question is how do I create this...

Since I would be filling this from Java, it would be a parameter within the procedure something like

CREATE OR REPLACE PROCEDURE Test_Response ( pisa_response Varchar[][] )

Of course Varchar[][] is not supported by PL but just showed this to give more understanding of the problem

Any help on this would be really appreciated..

Thanks in advance..

Regards,
FreakieCheck the thread in the Java section of this "dbForums" for details.|||Hi Thanks 4 the reply...

From what I gathered.. I might need to send it as a string...but I do not want to do that...

I basically want to send it as an array of strings... I am not sure whether using ORacle.sql.array feature might help???

Regards,
Freakie|||Again, check the thread in the Java section of this forums. Its got
complete code to perform what your looking for, so long as your
running Oracle 9i.

passing a new query to a report

hi all,

ok, here is my problem. my employer has tasked me to create fairly complex program.. and with it he wants a reporting system. now, i have been given permission to use VB Express, SQL Server 2005 Express with Advanced Features, and the SQL Server Express Toolkit for my application. my employer would prefer not to have to spend the money on visual studio professional (although, in truth it would make my life so much easier as reports are integrated into the IDE, via crystalreports), so i HAVE to do it the way of viewing all reports through ie.

now (after much toil) i have finally figured out how to display the reports in an internet explorer window (which i can also run from my application), and the report displays no problem! when i finally managed this i was through the bloody moon. now, what i would like to do is one of two things...

1) display a report based on a dynamic query from my vb.net form, so i can filter the results how i want

or

2) simply define a static query from within the report, create a couple of parameters in the report, and supply the parameters to the report from the vb.net form, via maybe the url of the report?

i have been thinking on this for a while and i have not been able to come up with anything, im hoping someone here will have gone through a similar problem and will be able to help me out

if anyone could provide advice, tutorials, links on how to go about accomplishing this i would be eternally grateful!!!

regards

adam

after quite a bit of resarch i found it is in the MSDN library, you just have to search sql server reporting services.

this only allows passing of parameters... but if anyone knows how to pass a completely new query it would be greatly appreciated!

http://msdn2.microsoft.com/de-de/library/ms153586.aspx

hope this helps somebody!

regards

adam

Pass Variables from Trigger to SP ?

Fairly new to triggers and stored procedures and so far I haven't had to pass any variables or even call an SP from a trigger. The problem I have is to pass a 'callid' variable from an insert trigger to a stored procedure.

The SP is to delete any rows that already exist that contain the passed 'callid' and then insert the new inserted data (using variables again hopefully).

Is this at all possible and if so what is the syntax for passing the variable from the trigger and then reading it into the stored procedure ?

TIASomething like:

CREATE TRIGGER trg_mytrigger ON tbl_my_table INSEAD OF INSERT
AS

DECLARE @.callid INT

SELECT @.callid = callid FROM inserted

--At this point, you can just to the delete from here, rather than calling a seperate sp_, so here are both ways:
EXEC sp_delete_rows_by_callid @.callid

--or
DELETE tbl_my_table WHERE callid = @.callid

--then do the insert - (you may want to be more explicit with field names here)

INSERT tbl_my_table
SELECT * FROM inserted

--end of trigger

sp_code:

CREATE PROCEDURE sp_delete_rows_by_callid
@.callid INT
AS
DELETE tbl_my_table WHERE callid = @.callid

-b|||Thanks bpdWork. I have modified the trigger as you suggested but I get an ODBC 3146 database error. Can you see where I may have got the syntax wrong. Thx.

CREATE TRIGGER trg_mytrigger ON callshistory INSTEAD OF INSERT
AS

DECLARE @.callid INT

SELECT @.callid = fkey FROM inserted as ins where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')

DELETE hold_complete WHERE fkey = @.callid

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
insert hold_complete
select ins.AddedDT, ins.fkey, ins.actiontext,
ins.subactiontext, con.emailaddress, ca.loggeddt,
(con.forename + ' ' + con.surname) as contactname,
ca.summary, ca.notes,co.coordinator, co.coordinator,getdate(), ca.status,ca.lastsubaction,getdate(),ca.dateopened ,ca.companyname,getdate(),(null),ch.notes
FROM inserted as ins with (nolock)
join calls as ca with (nolock)on
ins.fkey = ca.callid
join contact as con with (nolock) on
ca.contactid = con.contactid
join company as co with (nolock) on
ca.companyid = co.companyid
join callshistory as ch with (nolock) on
ins.historyid = ch.historyid
where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')|||I believe this error means Invalid Object Name, so the problem is probably one of your table names.

I'm not sure you can alias the "inserted" keyword. Try it without that.|||The syntax from SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED was used in my original trigger which allowed the duplicates so I know that bit works. I also tried the SP option you suggested and got no error message but no rows were deleted or inserted.

Can the inserted table be used outside of the original trigger for the SP ? When does it get deleted ?|||It's only around for the virtual instance the trigger is fired...once the trigger is done, it's gone...|||So should this work ? (BTW it don't.)

CREATE TRIGGER tr_DELETE_DUPES ON Hold_Complete
after insert AS

DECLARE @.callid varchar

SELECT @.callid = fkey FROM inserted

delete from hold_complete where addeddt <> (select max(addeddt)from hold_complete) and fkey = @.callid|||delete from hold_complete where addeddt NOT IN (select max(addeddt)from hold_complete) and fkey = @.callid|||I'm afraid that didn't delete the dupe rows either. Is there anyway that a message can be printed that will show the @.callid variable to make sure that's being captured properly|||You need to understand that the virtual tables will hold ALL of the affected rows...not just 1...your code will only grab the last one from the table...

you need to think set based...

And if you trying to DELETE dups AFTER the INSERT, I would think you would gewt a dup key error, and the trans would rollback and the trigger won't fire...

Assuming you have a pk contraint that is...

Also I would recommend removing the ISOLATION LEVEL code...

If you check @.@.ERROR in the sproc that is doing the INSERT you'll catch the error there...

You can then do an ipdate if you want to change the values...

I wouldn't do this in a trigger (Unless of course you don't have control over the DML, then I would)...|||CREATE TRIGGER tr_DELETE_DUPES ON Hold_Complete
after insert AS

DELETE
FROM hold_complete
INNER JOIN inserted
ON hold_complete.fkey = inserted.fkey
WHERE addeddt <> (select max(addeddt) from hold_complete)|||Brett

The hold_complete table is updated from an insert trigger on another table when certain criteria are met. This is unlikely to insert more than 1 row at a time. How would you suggest I go about deleting any previous entries with the same callid ?

bpd

It didn't like the join on a delete statement.|||My Bad:

DELETE hold_complete
FROM hold_complete
INNER JOIN inserted
ON hold_complete.fkey = inserted.fkey
WHERE addeddt <> (select max(addeddt) from hold_complete)|||Originally posted by Bracksboy
Brett

The hold_complete table is updated from an insert trigger on another table when certain criteria are met. This is unlikely to insert more than 1 row at a time. How would you suggest I go about deleting any previous entries with the same callid ?

bpd

It didn't like the join on a delete statement.

Cascading triggers?

Also it's not a matter of how likely an event can happen...it's whether it can or it can't...there are no colors here...it's either black or white...

It's not a matter of IF, it's a matter of WHEN

First, do you have a Primary Key contraint on CallId now?

If not, first find out what you're dealing with...

SELECT CallId, COUNT(*) FROM hold_complete GROUP BY CallId HAVING COUNT(*) > 1

If that returns nothing, you're in business...and just add the contraint, and you won't have to worry about the trigger at all...

If it does, then you need to sanatize the data, then add the contraint...|||Originally posted by Brett Kaiser

First, do you have a Primary Key contraint on CallId now?

If not, first find out what you're dealing with...

SELECT CallId, COUNT(*) FROM hold_complete GROUP BY CallId HAVING COUNT(*) > 1

If that returns nothing, you're in business...and just add the contraint, and you won't have to worry about the trigger at all...

If it does, then you need to sanatize the data, then add the contraint...

I don't have a constraint on callid and I don't think it will work with this. If I understand constraints correctly if I set one on callid it won't allow an insert where that callid already exists. Is that correct ?

The inserted data is not going to always have a unique callid so I just need to store the latest row of data and remove all previous rows with that callid. The rows that are inserted are tracking details from a call logging system that meet certain criteria and this can have multiple rows with the same callid. From this data I have a VB app that emails confirmation and then sets dates for chasers and automated closure. I need to delete the existing rows so only the latest info is used in the app.

It's all very new to me.|||You could always just do the delete first, then do an insert.|||but that's the whole point of a realtional database...

You should check to see if the row exists first...if it does grab it, and update it...

Then there's the problem if the row doesn't exist, you start enetering info, someone slides the key while you're typing and get it in...

You need to error handle and check @.@.Error to see if the execution was successful or not....

no MOO's about it...|||Thanks for your help guys but had a complete rethink and I have the application sorting the records I want using the following select statement in the ADO connection :

SELECT * FROM hold_complete a
WHERE exists (SELECT fkey FROM hold_complete b
GROUP BY fkey HAVING max(b.addeddt) = a.addeddt)
ORDER by fkey

Partitions and Slices without query binding

I have a fairly straightforward cube in SQL 2005 SSAS, with date as one of the dimensions. I created a partition for each year and quarter, and set the slice appropriately. However, in order to create the partition, I had to use a source query, because I got an error about re-using the same table. I then created a single partition that was based off the table and set a slice and reviewed the SQL that SSAS issued, and it did not restrict based on the slice.
My question is: Can you make SSAS (2005) work like SQL 2000 where it automatically generated (albeit not perfectly) a where clause to restrict the partition?
Thanks,
Doug

Hi Doug,

The way I got this to work is by using AMO to clone a base partition, and then update the underlying view (again in AMO by connecting to a SQL Server). So before the partition is processed, the view is updated.

I used Script Task in SSIS to do the AMO.

This is a nice generic solution.

Hope it helps.

Rahil

|||

Rahil,

Any reason that you didn't use the Analysis Services DDL Task in SSIS?

-Jamie

|||

Hi Jamie:

The AS DDL Task in SSIS will not dynamically create a new partition - whereas in the AMO or XMLA script you can achieve that. I guess - that you could argue that we can break that up into two steps and do the partition creation in code and then subsequent processing in the DDL Task.

Thanks.

Suranjan

Partitions and Slices without query binding

I have a fairly straightforward cube in SQL 2005 SSAS, with date as one

of the dimensions. I created a partition for each year and

quarter, and set the slice appropriately. However, in order to

create the partition, I had to use a source query, because I got an

error about re-using the same table. I then created a single

partition that was based off the table and set a slice and reviewed the

SQL that SSAS issued, and it did not restrict based on the slice.

My question is: Can you make SSAS (2005) work like SQL 2000 where

it automatically generated (albeit not perfectly) a where clause to

restrict the partition?

Thanks,

Doug

Hi Doug,

The way I got this to work is by using AMO to clone a base partition, and then update the underlying view (again in AMO by connecting to a SQL Server). So before the partition is processed, the view is updated.

I used Script Task in SSIS to do the AMO.

This is a nice generic solution.

Hope it helps.

Rahil

|||

Rahil,

Any reason that you didn't use the Analysis Services DDL Task in SSIS?

-Jamie

|||

Hi Jamie:

The AS DDL Task in SSIS will not dynamically create a new partition - whereas in the AMO or XMLA script you can achieve that. I guess - that you could argue that we can break that up into two steps and do the partition creation in code and then subsequent processing in the DDL Task.

Thanks.

Suranjan

PARTITION'ing Types

Hello All,
I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
2000.
I have to implement PARTITION feature that is in SQLServer 2005 and I was
going through BOL and I just wanted to clarify.
Are there Different Types of PARTITIONs. I'm asking since I would want to
chose the one that is optimal.
Thanks,
rgnHi rgn
It was originally planned that you could have either a RANGE partition or a
HASH partition, but HASH partitions did not make the cut. We only have RANGE
partitions.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:618481E3-2D7F-481D-9012-7B311D80BB8A@.microsoft.com...
> Hello All,
> I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
> 2000.
> I have to implement PARTITION feature that is in SQLServer 2005 and I was
> going through BOL and I just wanted to clarify.
> Are there Different Types of PARTITIONs. I'm asking since I would want to
> chose the one that is optimal.
> Thanks,
> rgn
>

PARTITION'ing Types

Hello All,
I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
2000.
I have to implement PARTITION feature that is in SQLServer 2005 and I was
going through BOL and I just wanted to clarify.
Are there Different Types of PARTITIONs. I'm asking since I would want to
chose the one that is optimal.
Thanks,
rgn
Hi rgn
It was originally planned that you could have either a RANGE partition or a
HASH partition, but HASH partitions did not make the cut. We only have RANGE
partitions.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:618481E3-2D7F-481D-9012-7B311D80BB8A@.microsoft.com...
> Hello All,
> I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
> 2000.
> I have to implement PARTITION feature that is in SQLServer 2005 and I was
> going through BOL and I just wanted to clarify.
> Are there Different Types of PARTITIONs. I'm asking since I would want to
> chose the one that is optimal.
> Thanks,
> rgn
>

PARTITION'ing Types

Hello All,
I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
2000.
I have to implement PARTITION feature that is in SQLServer 2005 and I was
going through BOL and I just wanted to clarify.
Are there Different Types of PARTITIONs. I'm asking since I would want to
chose the one that is optimal.
Thanks,
rgnHi rgn
It was originally planned that you could have either a RANGE partition or a
HASH partition, but HASH partitions did not make the cut. We only have RANGE
partitions.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:618481E3-2D7F-481D-9012-7B311D80BB8A@.microsoft.com...
> Hello All,
> I'm new to SQLServer 2005 though I have fairly good knowledge on SQLServer
> 2000.
> I have to implement PARTITION feature that is in SQLServer 2005 and I was
> going through BOL and I just wanted to clarify.
> Are there Different Types of PARTITIONs. I'm asking since I would want to
> chose the one that is optimal.
> Thanks,
> rgn
>