Solved

SQL Stored procedure

Posted on 2013-05-20
25
323 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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
I will show you the solution when I have it
0
 

Author Comment

by:1752
Comment Utility
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
Comment Utility
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
Comment Utility
So something like this

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

Author Comment

by:1752
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
I will try.

Thanks
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:1752
Comment Utility
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
Comment Utility
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
Comment Utility
what u r passing as input paramter.
pls share code
0
 

Author Comment

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

Author Comment

by:1752
Comment Utility
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
Comment Utility
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
Comment Utility
procedure and errors

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

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
I know i'm old school and have never been trained, asp/sqlvb/html I tought myself
0
 

Author Closing Comment

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

Regards
1752
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

7 Experts available now in Live!

Get 1:1 Help Now