Set value for variable in Store procedure

[Bullsandbears123]

I am trying to set a value based on a value in my VB code
EXAMPLE:
dim value as integer
value=5


I also have SQL
Create PROC Setvalue
@input
AS SELECT field1, field1+@input FROM table1

How do I assign a value to the variable. I need to know if my SQL looks correct and what to type in my VB code to assign the value.

How to make value=@input in VB for SQL
THanks

 

[NoCoolHandle]

Do you mean that you want to set a variable inside a stored proc, or that you want to return one??

To set a variable in a proc use..

Create Proc Demo
@input1 varchar(5),
@input2 varchar(6) = "default"
as
set nocount on
Declare @Internal_Variable
if @input2 <> &quot;default&quot;
begin
set @Internal_Variable = @input1
end
else
begin
set @Internal_variable = @input1 + @input2
end
Select @Internal_variable


(you can also use select to set a variable.. great in asmuch as you can set more than one variable at a time.

To return one to vb you need to have the variable declared as an output paramater and use and ado command object in an ado based application.

But you would also need to explicitly declare the commands commandtype property as being an adstoredproc (or something like that)

If you want a demo snip post back


Rob

 

[rasx]

I am not sure that I understand the motivation/focus around your question but here are some generalities:

There are two ways to set a variable in Transact-SQL. The first way uses the SET statement and the second way uses the SELECT statement.

So this is the VB:

Dim intValue as Integer: inValue = 5

And this is the tSQL:

DECLARE @inValue int
SET @intValue = 5

Or:

DECLARE @inValue int
SELECT @intValue = 5



Bryan Wilhite
info@songhaysystem.com

http://www.songhaysystem.com

 

[Bullsandbears123]

I have a value that is created in VB
&quot;value&quot; when VB sets this value I need to send it to a SQL statement parameter.

I think it does have something to do with declaring a variable as an output paramater and using ado command object. I am pretty sure I need to use commandtype and commandtext but I have absolutely no idea what to type in VB and my SQL statement
I demo would be great

THANKS
EXTRA INFO-
Basically I am trying to pass a value from VB into a parameter query.

SELECT field1, Field2+[input] FROM table1
or
SELECT field1, field2 FROM table1 WHERE field1=[input]
But I do not know how to, so I thought a stor Proc might be another way to do it. THANKS AGAIN

 

[hoggle]

What you'll need to do in vb6 is this: (using ado 2.6)
======================================================
Dim cm As Command
Dim prmValue As Parameter
Dim rs as RecordSet
Dim cn as Connection

'open database connection
cn.open &quot;Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=YourDataBase;Server=YourServerName;&quot;

Set cm = New Command

'set up command object to call stored procedure
cm.CommandText = &quot;YourDataBase..YourStoredProcedure&quot;
cm.CommandType = adCmdStoredProc

'set parameter for stored procedure
Set prmValue = cm.CreateParameter(&quot;input&quot;, adInteger, adParamInput, , value)

'append parameter to command object
cm.Parameters.Append Value

'set connection to database
cm.ActiveConnection = cn

'call stored procedure and return results into a recordset
Set rs = comZeroBase.Execute

==============================================
basically your stored procedure will look like this:

Create Procedure YourStoredProcedure
@input INT
as

--you can only use one of these two queries
--this will return 2 recordsets
--which you can have, but its a whole new can of worms
--to deal with
SELECT field1, Field2+ @input FROM table1

SELECT field1, field2 FROM table1 WHERE field1= @input

GO
===================================================


and thats it!

 

[NoCoolHandle]

I am not sure I am following you question either..

1. It seems like you are looking for code from VB and that you don't necessarly want to use a stored proc. Is that correct??

2. Where you intending to return a recordset (multiple records and columns) or was it a single value?

 

[Bullsandbears123]

Does it matter that I am using VBA in Access, not VB 6.0?

 

[NoCoolHandle]

No, but what do you want to do?

 

[Bullsandbears123]

I am trying to run SQL based on a value(parameter) in VB from access. I also noticed that &quot;hoggle&quot; has &quot;server&quot; & &quot;yourdatabase&quot;. What should I use for server? and I assume &quot;yourdatabase&quot; would be = to

dim db as database
Set db = CurrentDb()
then use &quot;db&quot; instead of &quot;yourdatabase&quot;
THanks

 

[Bullsandbears123]

I don't nessarily want to use stor proc, but I see no other way.

See I am trying to run a report that it dependent on the input values.

I have a report that pulls from a query. This query has fields that calculate items based on the input values. I have several forms in Access that use this query so I am trying to pass a variable into the sql and run the report. The problem comes from the fact that the original SQL get pulled from a table, so I can not just edit the SQL in VB and run the query. In addition, I can not use FORM!combobox.value in place of the @input because I use different forms and sometimes no forms. So basically I need to pass a value from VB into the SQL to run my report.

If I did not make it clear I can get more detailed.

THANKS

 

[NoCoolHandle]

What is the back end database that you are using? is it Access or SQL?

 

[Bullsandbears123]

Access

 

[Bullsandbears123]

I figured it out THANK YOU!