CREATE PROC PutYourNameHere
@User varchar(30),
@application int
AS
...your code here

GO

quote:

---

Originally posted by visakh16

Reading your requirement, I think what you're after is a stored procedure
The code above is just a stub for creating a procedure. The <> denotes place holders.
In your case it would be

CREATE PROC PutYourNameHere
@User varchar(30),
@application int
AS
...your code here

GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by KilpAr

quote:

---

Originally posted by visakh16

Reading your requirement, I think what you're after is a stored procedure
The code above is just a stub for creating a procedure. The <> denotes place holders.
In your case it would be

CREATE PROC PutYourNameHere
@User varchar(30),
@application int
AS
...your code here

GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

Thanks, I was able to create it!

This is now quite close to work, I need to post this question in VBA-centered forum too, but just in case, could you spot the error here:

Function LoadAuthorizationInfoFromSQL(iApp As Integer, strUser As String) As Variant

strConn = ConnectToSQL()

Set oConn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command

oConn.Open strConn

stProcName = "GetUserAuthForApp"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = oConn
cmd.CommandText = stProcName

Set prm = cmd.CreateParameter("@User", adVarChar, adParamInput, 7)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@application", adInteger, adParamInput)
cmd.Parameters.Append prm

rs.Open cmd.Execute

...and so on...

The error comes at that
rs.Open cmd.Execute
it says
Procedure of function 'GetUserAuthForApp' expects parameter '@User', which was not supplied


The beginning of the stored procedure looks now like this:
CREATE PROC GetUserAuthForApp
@User varchar(7),
@application int
AS

---

quote:

---

Originally posted by visakh16

what are values you pass for iApp and strUser?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

USE [MyDatabase]
GO

DECLARE	
@return_value int,
@application int,
@user varchar(7)
EXEC
@return_value = [dbo].[GetUserAuthForApp]
@user = 'kilar', 
@application = 1
GO

Function LoadAuthorizationInfoFromSQL(iApp As Integer, strUser As String) As Variant

Dim oConn As ADODB.Connection 'Object for connecting
Dim rs As ADODB.Recordset 'Object for recordset
Dim cmd As ADODB.Command
Dim prmUser As ADODB.Parameter
Dim prmApplication As ADODB.Parameter
Dim stProcName As String
    
    'Define connection string and open the connection
    strConn = ConnectToSQL()
    
    'Initialize variables for database connections
    Set oConn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command
    
    oConn.Open strConn
     
    stProcName = "[MyDatabase].[dbo].[GetUserAuthForApp]"
    
    cmd.CommandType = adCmdStoredProc 'Define the ADODB command
    cmd.ActiveConnection = oConn 'Set the command connection string
    cmd.CommandText = stProcName 'Define Stored Procedure to run
    
    Set prmUser = cmd.CreateParameter("@user", adVarChar, adParamInput, 7)
    prmUser.Value = "kilar"
    cmd.Parameters.Append prmUser
    
    Set prmApplication = cmd.CreateParameter("@application", adInteger, adParamInput)
    prmApplication.Value = 1
    cmd.Parameters.Append prmApplication
    
        
    'Execute stored procedure and return to a recordset
    rs.Open cmd.Execute
'already crashed by this point

USE [MyDatabase]
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[GetUserAuthForApp]
@user varchar(7),
@application int
AS
DECLARE @tempauthtable varchar(max);
DECLARE @querystring varchar(max);

SET @tempauthtable = '##UserAuthForApp_' + @user

SET @querystring =
'IF NOT EXISTS(SELECT * FROM tempdb.sys.objects WHERE name = ''' + @tempauthtable + ''') 
CREATE TABLE ' + @tempauthtable + 'tablestructurehere';
EXEC (@querystring);

--building a dynamic temp table here about 150 rows cut out
EXEC (@querystring);
SET @querystring = 'SELECT * FROM ' + @tempauthtable;
EXEC (@querystring);