SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need more than a view -> Am I looking for a SP?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KilpAr
Yak Posting Veteran

80 Posts

Posted - 02/13/2013 :  03:04:16  Show Profile  Reply with Quote
I have a query that uses variables and returns a normal resultset. I need to use this query in several programs. It has two parameters (which are currently set in the code like
SET @user = 'myname'; --User name
SET @application = '1'; --Application
).

I would want it to be something way simpler for the programs to use than copying the 200 rows of code to there. I'm looking for something like
Set ResultSet = LongQueryProgram(user, 1)
so what do I need on the SQL Server end? A stored procedure? How do I create that for this kind of case?

When I right click on Stored Procedure folder of my database and take "new stored procedure", it gives:

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

How do I go from here? What's a sysname? What's the difference between Procedure_Name and ProcedureName? Do I need greater than and less than characters in the syntax?

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/13/2013 :  04:52:35  Show Profile  Reply with Quote
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/

Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 02/13/2013 :  05:39:49  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/13/2013 :  05:48:59  Show Profile  Reply with Quote
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


try as above without @ sign

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


Edited by - visakh16 on 02/13/2013 05:49:31
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 02/13/2013 :  07:11:33  Show Profile  Reply with Quote
One clear error there is that I don't use the values anywhere - I have parameters, but no values assigned to them!

The code looks now like this

Dim strConn As String 'Connection string to SQL Server
Dim strSQLtoExecute As String 'SQL query string to execute
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 'Stored Procedure name


Function LoadAuthorizationInfoFromSQL(iApp As Integer, strUser As String) As Variant
'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 = "GetUserAuthForApp" 'Define name of Stored Procedure to execute.
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)
cmd.Parameters.Append prmUser
Set prmApplication = cmd.CreateParameter("application", adInteger, adParamInput)
cmd.Parameters.Append prmApplication

prmUser.Value = "'" & strUser & "'" 'Tried also without quotes, no difference
prmApplication.Value = iApp

'Execute stored procedure and return to a recordset
rs.Open cmd.Execute


and the error is now
"Arguments are of wrong type, are out of acceptable range, or are in confilict with one another"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/13/2013 :  07:23:30  Show Profile  Reply with Quote
what are values you pass for iApp and strUser?

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

Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 02/13/2013 :  07:42:52  Show Profile  Reply with Quote
The problem is now something in the stored_procedure, probably something in the way to return resultset. Where do I declare the return value in the stored procedure? When i create the procedure and run from management studio, it wants to return an int. How do I change that into a resultset?
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 02/13/2013 :  07:49:47  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

what are values you pass for iApp and strUser?

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





iApp = 1
strUser = kilar
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 02/13/2013 :  08:08:43  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
put
set nocount on
at the top of the sp
then put a select statement at the end to return the resultset.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 02/13/2013 :  10:20:34  Show Profile  Reply with Quote
After quite a bit of fight, it finally works from SQL Server Management Studio - but not from VBA

In SQL Server Management Studio I can run it like this:

USE [MyDatabase]
GO

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



From VBA, this doesn't work:

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


The stored procedure looks like:

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);


Error is still:
"Arguments are of wrong type, are out of acceptable range, or are in confilict with one another"
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 02/14/2013 :  09:50:45  Show Profile  Reply with Quote
Now the VBA part looks like this:

Set rs = cmd.Execute
rs.Open
Range("F1").CopyFromRecordset rs

and it complains the recordset is closed. So I do get the recordset, I just can't read anything from it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000