Solved

SQL Stored procedure

Posted on 2013-05-20
25
327 Views
Last Modified: 2013-05-21
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
Comment
Question by:1752
  • 14
  • 6
  • 4
  • +1
25 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39180400
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39180489
>>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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39180587
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:1752
ID: 39180596
I will show you the solution when I have it
0
 

Author Comment

by:1752
ID: 39180620
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39180637
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
 

Author Comment

by:1752
ID: 39180638
So something like this

Select colum name from table where columb name like parameter and ID = ID
0
 

Author Comment

by:1752
ID: 39180694
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
 

Author Comment

by:1752
ID: 39180914
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39180991
>>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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39181012
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
 

Author Comment

by:1752
ID: 39181024
I will try.

Thanks
0
 

Author Comment

by:1752
ID: 39181270
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
 

Author Comment

by:1752
ID: 39181450
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39181537
what u r passing as input paramter.
pls share code
0
 

Author Comment

by:1752
ID: 39181670
input parameter is a string  "Amount"
and the ID = 20 for test purpases
0
 

Author Comment

by:1752
ID: 39182047
SELECT [Amount]
FROM paymentresults
where PaymentResultsID ='20'

Turn this into a stored procedure using two varables @parameter "column name" and
@PaymentResultsID the ID
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39182490
How did you invoke the sproc to get the error in post 39181270? Post the statement that you have executed.
0
 

Author Comment

by:1752
ID: 39183877
procedure and errors

If I an get this to work it will work
1.bmp
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39183938
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
 

Author Comment

by:1752
ID: 39184056
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39184126
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39184139
@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
 

Author Comment

by:1752
ID: 39184178
I know i'm old school and have never been trained, asp/sqlvb/html I tought myself
0
 

Author Closing Comment

by:1752
ID: 39184341
I'm going to keep it simple and have a stored procedure for each column.

Regards
1752
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question