Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

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
0
1752
Asked:
1752
  • 14
  • 6
  • 4
  • +1
1 Solution
 
PaulCommented:
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)
0
 
PaulCommented:
>>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.
0
 
Bhavesh ShahLead AnalysistCommented:
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?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
1752Author Commented:
I will show you the solution when I have it
0
 
1752Author Commented:
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
0
 
Bhavesh ShahLead AnalysistCommented:
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
0
 
1752Author Commented:
So something like this

Select colum name from table where columb name like parameter and ID = ID
0
 
1752Author Commented:
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
0
 
1752Author Commented:
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
0
 
PaulCommented:
>>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?
0
 
Bhavesh ShahLead AnalysistCommented:
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

0
 
1752Author Commented:
I will try.

Thanks
0
 
1752Author Commented:
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
0
 
1752Author Commented:
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
0
 
Bhavesh ShahLead AnalysistCommented:
what u r passing as input paramter.
pls share code
0
 
1752Author Commented:
input parameter is a string  "Amount"
and the ID = 20 for test purpases
0
 
1752Author Commented:
SELECT [Amount]
FROM paymentresults
where PaymentResultsID ='20'

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

If I an get this to work it will work
1.bmp
0
 
PaulCommented:
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

0
 
1752Author Commented:
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
0
 
PaulCommented:
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.
0
 
PaulCommented:
@1752 please take no offence by this question, but do you speak another language?

I am just concerned that you are not using SQL properly - it appears to me that you will use VB in a loop and expect SQL to provide just one item of data (cell by cell). This would be very inefficient.
0
 
1752Author Commented:
I know i'm old school and have never been trained, asp/sqlvb/html I tought myself
0
 
1752Author Commented:
I'm going to keep it simple and have a stored procedure for each column.

Regards
1752
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 14
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now