Link to home
Create AccountLog in
Avatar of 1752
1752Flag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Stored procedure

Dear Experts,

This is what I want to do;

I have a table and wish to extract just a colomb using an ID that is from the  indentity ID.

So I have setup the function that I send the parameter to with the ID for a row.

SQl Procedure looks for the Row (ID) and returns the Columb data, select ID and return the data with the same columnb name as the Parameter.

I'm using case select in the VB function and a sqlreader to return the DATA.

Function

       Function Select_Indvidual_PaymentResults(ByVal PaymentResultsID As Integer, ByVal Parameter As String) As Integer
        Dim ReturnData As Integer = Nothing
        Dim cmd1 As New SqlCommand
        Dim Dr1 As SqlDataReader
        cmd1.CommandText = "Select_Indvidual_PaymentResults"
        cmd1.CommandType = Data.CommandType.StoredProcedure
        cmd1.Connection = sqlConnection
        cmd1.Parameters.Add("@Parameter", Data.SqlDbType.Int).Value = Parameter
        sqlConnection.Open()
        Dr1 = cmd1.ExecuteReader
        Do While Dr1.Read
            Select Case Parameter
                Case "Amount"
                    ReturnData = Dr1.Item("Amount")
                Case "AuthCode"
                    ReturnData = Dr1.Item("tomTransactionOutputMessageAuthCode")
            End Select
        Loop
        Dr1.Close()
        sqlConnection.Close()
        Return ReturnData
    End Function

Regards 1752
Avatar of PortletPaul
PortletPaul
Flag of Australia image

I'm not clear on what you are asking for, a sql query that might suit would look like this:

select
case when @parameter = 'Amount' then [some_value] else [different_value] end as the_return_value
from unspecified_table
where id = @PaymentResultsID

what are the table and field names involved? (I don't see any in the question)
>>Bollocks ir will
We do look back at past questions. I just did to see if I could learn more about this question. Regret to say I didn't find anything there that helps me understand what you really need on this question.

I shall wait until you have clarified what you need.
hi,

what i understood from question is you want view data based on passing input parameter.

so if you pass parameter as Amount and Id.
you want to view the data of Amount column.

Am i right?
Avatar of 1752

ASKER

I will show you the solution when I have it
Avatar of 1752

ASKER

Hi Brichsoft,

"you want to view the data of Amount column."

Nearly I want to get the "amount" columb with the same ID. So any cell in the row of the ID and the cell being what the name of the parameter.

Regards
1752
ok.what will be your filter condition.
e.g.
table1
id             as identity
customer
amount
description

by passing amount text as paramter you want to have a value of amount along with id.

then what will be your filter condition
Avatar of 1752

ASKER

So something like this

Select colum name from table where columb name like parameter and ID = ID
Avatar of 1752

ASKER

Rank: Sage
ok.what will be your filter condition.
e.g.
table1
id             as identity
customer
amount
description

by passing amount text as paramter you want to have a value of amount along with id.

then what will be your filter condition  ID
Avatar of 1752

ASKER

by passing amount text as paramter you want to have a value of amount along with id.

then what will be your filter condition  ID

yes
>>I will show you the solution when I have it
I thought we trying to help you arrive at the solution...

>>Nearly I want to get the "amount" columb with the same ID.
>>So any cell in the row of the ID and the cell being what the name of the parameter.

so:
You want the value held in  any single column for a specified id=

select <<specified column name>>
from tableA
where id = <<a parameter>>

Is this what you are asking for?
Is This what u looking for ..
ALTER PROC sp_getTableValue 
	@FIELD_NAME varchar(50),
	@ID numeric
AS

DECLARE @STR_SQL varchar(1000)
SET @STR_SQL = 'SELECT ' + @FIELD_NAME + ' FROM SYSOBJECTS WHERE ID = ' + cast(@ID as varchar(50))

SELECT @STR_SQL 
EXEC (@STR_SQL)

Open in new window


EXEC sp_getTableValue '[name]',@id = 4

Open in new window

Avatar of 1752

ASKER

I will try.

Thanks
Avatar of 1752

ASKER

ALTER PROC [dbo].[Select_Indvidual_PaymentResults]
      @Parameter varchar(50),
      @PaymentResultsID int
AS

DECLARE @STR_SQL varchar(1000)
SET @STR_SQL = 'SELECT ' + @Parameter + ' FROM SYSOBJECTS WHERE ID = ' + cast(@PaymentResultsID as varchar(50))

SELECT @STR_SQL
EXEC (@STR_SQL)

I Get

Exception Details: System.IndexOutOfRangeException: Amount

Source Error:


Line 23:             '        ReturnData = Dr1.Item("tomTransactionOutputMessageAuthCode")
Line 24:             '    Case "Amount"
Line 25:             ReturnData = Dr1.Item("Amount")
Line 26:             'End Select
Line 27:         Loop
Avatar of 1752

ASKER

You want the value held in  any single column for a specified id=

select <<specified column name>>
from tableA
where id = <<a parameter>>

Is this what you are asking for?  Yes
what u r passing as input paramter.
pls share code
Avatar of 1752

ASKER

input parameter is a string  "Amount"
and the ID = 20 for test purpases
Avatar of 1752

ASKER

SELECT [Amount]
FROM paymentresults
where PaymentResultsID ='20'

Turn this into a stored procedure using two varables @parameter "column name" and
@PaymentResultsID the ID
How did you invoke the sproc to get the error in post 39181270? Post the statement that you have executed.
Avatar of 1752

ASKER

procedure and errors

If I an get this to work it will work
1.bmp
numerous problems:
ALTER PROC [dbo].[Select_Indvidual_PaymentResults] 
      @Parameter varchar(50),
      @PaymentResultsID int
AS
set @parameter = "Amount" /* not required anyway; but also no double quotes, us single quotes */
set @PaymentResultsID = 1 /* not required this is a parameter */

SELECT [@Parameter] /* this won't work you need dynamic sql to achieve this*/
FROM PaymentResults
where PaymentResultsID = @PaymentResultsID

Open in new window

Avatar of 1752

ASKER

Thanks,

This is my confusion as never done any dynamic sql.

So if you could provide me with the correct dynamic sql I would be a happy man.

Cheers 1752
please go to this URL http://sqlfiddle.com/#!3/f03f7/3

there you will see I have created a simple table (on the left)
on the right there is code to create the stored procedure, and under that there are 3 separate executions of the procedure. On the right is a blue button [Run SQL] so you can see what happens when the displayed code is executed

The only difference between this code and the code by: BrichsoftPosted  at ID: 39181012 is the table name and I have removed the 'select' as it isn't required.
ALTER PROC sp_getTableValue 
	@FIELD_NAME varchar(50),
	@ID int
AS

DECLARE @STR_SQL varchar(1000)
SET @STR_SQL = 'SELECT ' + @FIELD_NAME + ' FROM PaymentResults WHERE ID = ' + cast(@ID as varchar(50))

EXEC (@STR_SQL)
;

Open in new window

so that is the procedure. Below are some examples of how to use it:
EXEC sp_getTableValue 'Amount',3
;

EXEC sp_getTableValue 'Other',2
;

EXEC sp_getTableValue 'ID',1
;

Open in new window

Note: The procedure will fail if the field does not exist in the table.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 1752

ASKER

I know i'm old school and have never been trained, asp/sqlvb/html I tought myself
Avatar of 1752

ASKER

I'm going to keep it simple and have a stored procedure for each column.

Regards
1752