Temporary Store Procedure 1

[sqldevrequiretektips]

If i create Temporary store procedure from VB on SQL server
and My application has more then 10 users..

so How does sql server handle this kind situation when multiple user are trying to create same name temporary procedure.

Is it really possible?

I have ask this question to VB developer but i didn't get any response...

so if anybody know about this, please help me out

Let me show you my code where i am creating store procedure.

Create the stored procedure we will work with
CreateStoredProcedure = False
On Error GoTo errorhandler
Const strLcPROCEDURE_NAME As String = "CreateStoredProcedure"
Dim cmdLvCommand As Command
Dim strSQL As Variant
Set cmdLvCommand = New Command

'First Check if the store procedure allreday exists
'cmdLvCommand.CommandType = adCmdText
cmdLvCommand.CommandText = "If Exists (select name from sysobjects "
cmdLvCommand.CommandText = cmdLvCommand.CommandText & _
"Where name = " & "'" & strMvStoredProcedureName & "'" & ")"
cmdLvCommand.CommandText = cmdLvCommand.CommandText & " Drop Procedure " & strMvStoredProcedureName
Set cmdLvCommand.ActiveConnection = cnMvConnection
cmdLvCommand.Execute
Set cmdLvCommand = Nothing
'Set cmdLvCommand.ActiveConnection = Nothing
Set cmdLvCommand = New Command

If strMvStoredProcedureName = "P_Retrive_Dowload_data_WIActCat_WIDay" Then
cmdLvCommand.CommandText = "CREATE PROCEDURE " & strMvStoredProcedureName
cmdLvCommand.CommandText = cmdLvCommand.CommandText & " AS "
cmdLvCommand.CommandText = cmdLvCommand.CommandText & " SET NOCOUNT ON "
cmdLvCommand.CommandText = cmdLvCommand.CommandText & "DECLARE @Error AS Integer "
strSQL = " Select A.CustLifeNo, A.WIActCatId, A.WIDayId, A.WeekNo " & _
"from WIActCat_WIDay A " & _
"Inner Join (select distinct (cust_life_num) from CUSTTB where " & where_sqldb & " ) B" & _
" On A.CustLifeNo = B.Cust_Life_num"


Debug.Print strSQL
cmdLvCommand.CommandText = cmdLvCommand.CommandText & strSQL
cmdLvCommand.Name = "CreateSP"
With cmdLvCommand
Set .ActiveConnection = cnMvConnection
.CommandType = adCmdText
.prepared = True
.Execute
End With

 

[SQLSister]

Why are you doing this? Why not just create a stored procedure and let different users exec it? What is the reason behind creating a temporary stored procedure? I don't see where this gains you anything over sending dynamic SQL code directly from the user interface and in fact it seems to me it would be less efficient since you also have the create procedure overhead as well as running the query.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[ProdAir]

If you're doing this because you want to build your stored proc on the fly, you may want to consider just using dynamic sql with sp_executesql. And you may want to close your connection right after you call the second execute method unless you need to use it again...

 

[sqldevrequiretektips]

Thx for your reply..

Let me tell you in detail..So if i am going on wrong direction then please guide me...


strSQL = " Select A.CustLifeNo, A.WIActCatId, A.WIDayId, A.WeekNo " & _
"from WIActCat_WIDay A " & _
"Inner Join (select distinct (cust_life_num) from CUSTTB where " & where_sqldb & " ) B" & _
" On A.CustLifeNo = B.Cust_Life_num"

Above query which i am passing to storeprocedure.
Table Name WIActCat_WIDay has more then 8000000 Records
If i execute this query by using link table on Ms access
It's take for so long time to run..

let me tell you what i am trying to achieve :
SQL Server Tables
WIActCat_WIDay
Custtb - 500000 (Total rows)

SQLDB string (Where clause) Expression for an Example:-
This sqlDB depends on what user select on vb form then i am passing this values to my store procedure...

(Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 1) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 2) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 6)

strSQL = " Select A.CustLifeNo, A.WIActCatId, A.WIDayId, A.WeekNo " & _
"from WIActCat_WIDay A " & _
"Inner Join (select distinct (cust_life_num) from CUSTTB where " & where_sqldb & " ) B" & _
" On A.CustLifeNo = B.Cust_Life_num"

So using above query in storeprocedure which return the recordset..If you see strSQL which i am executing on command object so when i called

With cmdLvCommand
Set .ActiveConnection = cnMvConnection
.CommandType = adCmdText
.prepared = True
set rs= .Execute
End With

set rs where i am catching the recordset...
So, My question is whatever i am doing is that right..
Is there anyway we can create permanant query which return the recordset (That would be really great) ..

F.Y.I. - > In my case sqldb(Where clause) always get changed...

 

[sqldevrequiretektips]

Thanks to SQL Sister & ProdAir

If I create a storeprocedure with input and output parameter that could make life easier..and better

but since i don't have more experience with storeprocedure
if you could help me to write stroprocedure would be really great....

AD

 

[SQLSister]

Well the answer to that is it depends. Don't you love those wishy washy answers.

If your where clause is limited to a specific set of fields that may or may not be searched on, it is isn't too hard to build an sp to account for that. If you need to use the in clause it's harder but doable, if you need to combine and and or and in and like etc. then you need dynamic SQL which is less secure and harder to debug and less efficient. In fact it's pretty much like creating the SQL directly in the user interface and sending it except you send slightly less information through the network.




Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[sqldevrequiretektips]

Thanks for your reply..

How do i create a storeprocedure which accept whereclause as input parameter and output parameter will be my recordset..

Just for your information

where clause - which is SQLDB string, What user have selected from the VB form.

Somehow whatever user selected I am storing in SQLDB.

Example how SQLDB Looks like :-

SQLDB = (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 1) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 2) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 6)

Sometime
SQLDB = (Region_Num = 47) OR (Region_Num = 48) OR (Region_Num = 50)

Sometime :-
SQLDB = (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 1) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 2) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 3) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 4) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 6) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 7) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 8) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 9)


so now how would i pass this whole string (SQLDB) to store procedure and what will be inside my storeprocedure..

Please help me out SQL Sister whenver you get a chance..

I really wants to get this thing work..

Thanks
AD

 

[SQLSister]

So let me get this straight, you have three fields that you can search on, Region_Num, Team_Num Territory_Num. You could search on any one of these and each could contain multiple values? All the field are integer fields?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[sqldevrequiretektips]

Region_Num, Team_Num, Territory_num all are Integer fields

You absulutaly right that i can search on any of these fields and each could have multiple values...


These all fields in table called CUSTTB


Let me know if you need any more info..

Thanks for your concern...
AD

 

[SQLSister]

OK here's some reading to get you started.
Passing a list of values to a Stored Procedure faq183-3979
Passing a list of values to a Stored Procedure (Part II) faq183-5207

It may be, prolly will be, tomorrow before I can give you a way to do this, but you'll need to understand the concepts in these FAQs first.


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[ProdAir]

And if you're passing in a really large number of values, you may wanna look into using SQL Server's XML support (if you're using SQL2K or later). The only thing to remember is that when you use SQL Server's XML capabilities, it can eat up a lot of memory since it will need to create the DOM in memory. So you'll have to weigh the costs and the benefits. Good luck!