Solved

SQL Stored procedure

Posted on 2013-05-20
25
324 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
 

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now