Return Column Names Where Values Were Not Found [stored Procedure]

Sep 5, 2004

I need to check whether procedure found any matches or not. If not it has to return the column name where matching value was not found. For example, if there was no record found in the table "Addresses" column "customer" with the value @username, it should return "street". If id with value @prod_id was not found in the table "Products", the "productname" must be returned as well.

CREATE PROC sp_test
@id INT,
@username VARCHAR(50),
@prod_id INT
AS

SELECT name FROM Customers WHERE id=@id
SELECT street FROM Addresses WHERE customer=@username
SELECT productname FROM Products WHERE id=@prod_id


It is kind of check, which has to find out if users have inserted all the necessary values or not.
Thanks for any advice.

View 4 Replies


ADVERTISEMENT

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

Return Values With Stored Procedure

Jun 4, 2004

Hello Group
I am new to stored procedure and I need some assistants. I am using the following stored procedure and I would like the return the fldPassword and fldFullName values. Both fields are in the same table. What I am trying to do is display the uses full name (i.e. Welcome <full Name>) and with the password I want to check that the password is not the default password if it is then do a redirect to the change password page.
Thank you
Michael


CREATE PROCEDURE stpMyAuthentication
(
@fldUsername varchar( 50 ),
@fldPassword Char( 25 ),
@fldFullName varchar( 75 ) OUTPUT
)
As
DECLARE @actualPassword Char( 25 )
SELECT
@actualPassword = fldPassword

FROM [tbUsers]
Where fldUsername = @fldUsername
IF @actualPassword IS NOT NULL
IF @fldPassword = @actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
GO


code


Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)
If IsValid Then
If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then
FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)
End If
End If
End Sub

Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
Dim strFullName As String
' Variable Declaration
Dim myConn As SqlConnection
Dim myCmd As SqlCommand
Dim myReturn As SqlParameter
Dim intResult As Integer
Dim sqlConn As String

' Set conn equal to the conn. string we setup in the web.config
sqlConn = ConfigurationSettings.AppSettings("sqlDbConn")
myConn = New SqlConnection(sqlConn)

' We are going to use the stored procedure setup earlier
myCmd = New SqlCommand("stpMyAuthentication", myConn)
myCmd.CommandType = CommandType.StoredProcedure

' Set the default return parameter
myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
myReturn.Direction = ParameterDirection.ReturnValue

' Add SQL Parameters
myCmd.Parameters.Add("@fldUsername", strUsername)
myCmd.Parameters.Add("@fldPassword", strPassword)
myCmd.Parameters.Add("@fldFullName", strFullName)

' Open SQL and Execute the query
' Then set intResult equal to the default return parameter
' Close the SQL connection
myConn.Open()
myCmd.ExecuteNonQuery()
intResult = myCmd.Parameters("RETURN_VALUE").Value
Session("strFullName") = strFullName
myConn.Close()

Response.Write(strFullName)
' If..then..else to check the userid.
' If the intResult is less than 0 then there is an error
If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!<br><br>"
Else
lblMessage.Text = "Invalid Password!<br><br>"
End If
End If
' Return the userid
Return intResult

End Function

View 1 Replies View Related

Please Help... Stored PRocedure Return Values

May 9, 2008

Hi,
I am trying to get the combination of results in my stored procedure, I am not getting what I need. Following are the things which I need to return from my stored proc.
1. I need to select distinct categories and their record count
2. I also need to select the records from the table.
3. Need to send both category, record counts and records.
First is it possible in stored procedure?
Following is helpful information.

Data in tables looks like this.
prod id, prod_name, prod_category
1, T shirts, Mens Apparel
2 , Shirts, Mens Apparel
3 , Pants , Mens Apparel
4, Tops , Women Wear
5, Bangles, Women Wear

And in User Interface I need to show like this.

Mens Apparel (3)
1 T Shirts
2 Shirts
3 Pants

Women Wear (2)
4 Tops
5 Bangles

Please help me if there is any way to return the complete data structure using stored procedure. If I do something in java code, I can get this, but I am trying to get directly from stored procedure only.

Thanks in advance...
Chandrasekhar

View 1 Replies View Related

How To Return More Than Two Values From A Stored Procedure..

Aug 13, 2007



Hi,


I have a requirement to get two count values from a stored procedure and use those values in other stored procedure.
How can I do that. I'm able to get only 1 value if i use the return key word.

Eg:

create proc test1 as
Begin
Declare scount int
Declare scount2 int
-- statements in stored procedure
return scount
return scount2
End


create proc test2

Declare variables...
Exec Test1
// here i want the values (scount and scount2 ), processed in stored procedure Test1 .

How can i get this.. please let me know..

Thanks,
srikanth

View 7 Replies View Related

Return Values From A Crosstab Stored Procedure

Jul 20, 2005

I came across an article in SQL Mag about Crosstab Queries. It worksgreat in Query Analyzer, but I'm stuck on how to use it in an AccessADP. I need to use it as a Recordsource in a form and report. Cansomeone tell me how to use it, and please try to be as descriptive aspossible. I'm new to Stored Procedures.Thanks*****************************************CREATE PROC sp_CrossTab@table AS sysname, -- Table to crosstab@onrows AS nvarchar(128), -- Grouping key values (on rows)@onrowsalias AS sysname = NULL, -- Alias for grouping column@oncols AS nvarchar(128), -- Destination columns (on columns)@sumcol AS sysname = NULL -- Data cellsASDECLARE@sql AS varchar(8000),@NEWLINE AS char(1)SET @NEWLINE = CHAR(10)-- step 1: beginning of SQL stringSET @sql ='SELECT' + @NEWLINE +' ' + @onrows +CASEWHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsaliasELSE ''ENDCREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)DECLARE @keyssql AS varchar(1000)SET @keyssql ='INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +'FROM ' + @tableEXEC (@keyssql)DECLARE @key AS nvarchar(100)SELECT @key = MIN(keyvalue) FROM #keysWHILE @key IS NOT NULLBEGINSET @sql = @sql + ',' + @NEWLINE +' SUM(CASE CAST(' + @oncols +' AS nvarchar(100))' + @NEWLINE +' WHEN N''' + @key +''' THEN ' + CASEWHEN @sumcol IS NULL THEN '1'ELSE @sumcolEND + @NEWLINE +' ELSE 0' + @NEWLINE +' END) AS c' + @keySELECT @key = MIN(keyvalue) FROM #keysWHERE keyvalue > @keyENDSET @sql = @sql + @NEWLINE +'FROM ' + @table + @NEWLINE +'GROUP BY ' + @onrows + @NEWLINE +'ORDER BY ' + @onrows-- PRINT @sql + @NEWLINE -- For debugEXEC (@sql)GO

View 6 Replies View Related

Transact SQL :: Stored Procedure Return Values

Sep 23, 2015

If I create a stored procedure and do not specify a return value or type, why does SSMS show that the stored procedure returns an int in the object explorer?  Is that simply the success flag?

View 5 Replies View Related

Variables As Table Or Column Names In A Stored Procedure

Apr 16, 2008

I would like to use variables to set the table name and some column names of a SQL Query in a stored procedure (the variable values will come from a webpage)... something like this:ALTER PROCEDURE dbo.usp_SelectWorkHours
@DayName varchar,@DayIDName varchar
AS
BEGINSELECT @DayName.InTime1, @DayName.OutTime1, @DayName.InTime2, @DayName.OutTime2 FROM @DayName
INNER JOINWorkHours ON @DayName.@DayIDName = @DayName.@DayIDName
INNER JOINEmployees ON WorkHours.WorkHoursID = Employees.WorkHoursID
END
RETURN
...is this possible?? if so how?
Thanks

View 2 Replies View Related

JDBC: Calling A Stored Procedure With Multiple Return Values.

Jul 23, 2005

Using JDBC, is there a way to call a stored procedure with multiplereturn values? Thanks.

View 4 Replies View Related

Passing A Column Of Values To The Stored Procedure

Apr 18, 2006

how can i send a column of values to the stored procedure for filtering that stored procedure values...?

View 1 Replies View Related

Return Tables That Have Certain Column Names

Feb 28, 2008

Hi all,

From the INFORMATION_SCHEMA.TABLES view I want to return the TABLE_NAME of tables that have columns say, named Email and EmailStatusId. Is it possible to do this with a single select statement or would I have to use two selects for this?

Please advise.

Thanks in advance.

View 7 Replies View Related

SELECT Statement To Return Table Column Names

Aug 16, 2004

Is there a SELECT statement that will return a the column names of a given table?

View 5 Replies View Related

Table Column Names = Dataset Column Values?!

Apr 28, 2008



I need to create the following table in reporting services



PRODUCT April March Feb

2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6





My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???


Thanks in advance!!!

View 1 Replies View Related

T-SQL (SS2K8) :: Get Column Names Where Values Are Not Matching

May 20, 2014

We have 2 tables (table a and b)

select a.* from table a inner join table b
on a.col1<> b.col2

I would like to have column names where the values are not matching.

View 4 Replies View Related

SQL 2012 :: Replacing Column Names With Values

Nov 4, 2015

I have a field in a table that contains a different formula (varchar(1000)) for each record. It's along the lines of something like this, although each formula is different: ([ColumnA] - [ColumnB])/([ColumnC] - [ColumnD]). I plug that into a dynamic SQL statement so that it can get executed in a select statement.

Due to the variations of the formulas, checking for Divide by Zero, etc, we want to move this to a .NET method. We'd like to replace "ColumnA" and "ColumnB", etc., with the actual values so that we're passing something like (5-3)/(6-2). I haven't been able to figure out a way to do this without actually executing it. We don't want to pass the solution, but the equation filled with the actual values rather than the column names.

View 3 Replies View Related

Make A View With Column Names From Row Values

May 22, 2008

Hi,

I have a table of results for various measured quantites and i need to turn this into a view. Only problem is i need to seperate the measured quantities and their respective values into seperate columns.

At the moment I have something like:

Quantity : Value
--------------------
Quantity 1 : 0.12
Quantity 1 : 0.56
Quantity 2 : 2.36
Quantity 2 : 5.34
Quantity 2 : 4.13
Quantity 3 : 10
Quantity 3 : 15

and I need a view that looks like:

Quantity 1 : Quantity 2 : Quantity 3
-------------------------------------
0.12 : 2.36 : 10
0.56 : 5.34 : 15
null(?) : 4.13 : null(?)

I've tried using pivots but they don't seem to help

Any ideas?

View 7 Replies View Related

Using Values Returned From SQL For Report Column Names

Apr 17, 2008

I am building reports against our TFS development db.
One of the reports tracks days spent (Dwell Time) in various status categories (eg: New, Assigned, In Development, Hold, etc) for a given "ticket".
For a fixed list of values from {Work Item].System_State, I can send the results (days in Assigned) to the column named (Assigned) for each status for each event in the [Work Item History], and then sum them for each ticket as:
Ticket ID New Assigned InDev etc
1230001 2 0 0 ...
1230001 0 1 2 ....

SUM 2 1 2 ....
However, I have many different Projects, each of which use their own Status names.
I don't want to duplicate the same basic report, if I can avoid it.

How can I name and generate this data for the unique Status list for each Project?

Simplest analog is: name = First(Fields!Status.Value, "TFSdb")
and allows value for a column name (category) as: =IIF(Fields!Status.Value = First(Fields!Status.Value, "TFSdb"), Fields!Days.Value, 0)
However this fails beause:
1. It only delivers the FIRST status value, and,
2. I cannot SUM an expression which is itself an aggregate (using First).

View 4 Replies View Related

Getting Column Names And Its Values Based On Condition

Sep 26, 2007



Hi,
I have a table as follows
Table Master
{

Id varchar(20),
Cat1 datetime,
Cat2 datetime,
Cat3 datetime,
Cat4 datetime
}

and the data in the table is as follows

Table Master
{

Id cat1 cat2 cat3 cat4
-----------------------------------------------
1 d11 null d13 d14
2 d21 d22 d23 d24
3 NULL d32 d33 d34
4 d41 d42 NULL NULL
}



I want to retrive column names and its values wheb the ID matches to some value.

Can any one please let me know how to do this?
Thanks alot
~Mohan

View 3 Replies View Related

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008


I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.

I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.

I have two parameters mapped:

tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1

I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.

The first part of the sp is below and I set the value @i and return.


CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output

as

Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.

View 2 Replies View Related

Choosing Between Two Column Values To Return As Single Column Value

Sep 14, 2007

I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid       user2_guidI am trying to write a query to return a single list of all a users' friends in a single column.  Depending on who initiates the friendship, a users' guid value can be in either of the two columns.  Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way...  Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))  

View 1 Replies View Related

Row Values As Column Names In Sql Server 2005-any Ideas?

Jul 31, 2007



The table tA contains (here I m using storedprocedure to return the values as columns varchar(8000))

ID LocationName

1 Door

2 FrontCounter



The number of records may vary like sometimes it have 2 or 3 or 4 or more records

So I have to return row values as column name.



tAChild
POSID storeID tAID Auth

1 140 1 true

1 140 2 false





Result Should be like ( to create this I m using SP with Dynamic sql query to create temp table)

POSID storeID Door Window

1 140 true true


if more records in tA it should append in the end of result as new column...

Is this the above is right way to implement ?any other ideas about this...
Please suggest me......

View 4 Replies View Related

Display Column Names And Its Values Programatically From The Dataset

Apr 18, 2008

Hi.

In my report, I need to display column names (and its values) from my dataset, which uses the stored procedure and within it use the PIVOT statement. I cannot use the matrix control in report since I need to add another static column to the right of the matrix, but since that cannot be done ni SQL SSRS 2005, I need to PIVOT it through stored procedure. The parameter passed to stored procedure is the year and it is used for pivot. The statement looks like this:

@sql = 'SELECT * FROM (SELECT YearId, SchoolId, ReportText, OutputValue_Text from AggregateTable) AS AGAM
PIVOT (MIN(OutputValue_Text) FOR YearId IN ([' + @YearId + '])) PVT'

EXEC(@sql)

So, since the years passed as parameters can change, I cannot hard-code it as column in my report, so I need to programatically display column names and values from my dataset, something like dataSet.Tables[0].Columns[].ColumnName; I have tried with writing the code, but I do not have much experience with it and I have not been very successful. Can someone help me with the code?

Thanks

View 2 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

SQL Server 2012 :: Add Column Names As Total And Subtotal For NULL Values?

Jan 21, 2014

How do I add column names as Total and SubTotal for NULL values.

SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)

[code]....

View 9 Replies View Related

Stored Procedure '' Not Found

May 10, 2007

Hello, I try to use Stored Procedure with SQL Server 2005. I was created a new procedure in my dbb "GetIDTransaction" set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetIDTransaction]
-- Add the parameters for the stored procedure here
@DateTransation NVarChar(50),
@ID int OUTPUT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

INSERT INTO Transactions_Guichet (DateTransaction, RefExpay,Montant,AccesRestreint,Valide,AdresseEmail,ClePerso,RemonteeBackOffice) VALUES(@DateTransation,'', '0','0','0','','','')
SET @ID=SCOPE_IDENTITY()
RETURN
  in   SqlConnection oConnection = new SqlConnection(strConString);
SqlCommand oCommand = new SqlCommand("GetIDTransaction", oConnection);
oCommand.CommandType = CommandType.StoredProcedure;
//Création de la dateTransaction
SqlParameter oParam = oCommand.Parameters.Add("@DateTransation", SqlDbType.DateTime, 8, "DateTransation");
oParam.Value = DateTime.Now.ToString();

SqlParameter oParam2 = oCommand.Parameters.Add("@ID", SqlDbType.Int, 0, "IdTransaction");

oParam2.Direction = ParameterDirection.Output;
//Ouverture de la connection
oConnection.Open();

//Récupère Id
oCommand.ExecuteNonQuery();
iIDTransation = Convert.ToInt32(oParam2.Value);
But i have always the same error Stored Procedure '' not found Thanks for you help 

View 7 Replies View Related

Stored Procedure Cannot Be Found

Jan 22, 2008

I'm trying to fill a dataset with the following code, but i'm getting an error that the stored procedure cannot be found. Do you see anything I could be doing wrong? Thanks
 1 Dim ds As New DataSet
2 Dim sda As SqlDataAdapter
3 Dim strSQL As String
4
5
6 Dim conn As New SqlConnection(Application("ConnectionString"))
7 conn.Open()
8
9 strSQL = "getEmployeInfo_proc '" & strOfficeID & "'"
10 sda = New SqlDataAdapter(strSQL, conn)
11 sda.Fill(ds, "Attorneys")
12 dgResults.DataSource = ds.Tables("Attorneys")
13 dgResults.DataBind()
14 conn.Close()
 

View 3 Replies View Related

Return Multiple Values In 1 Row Sep Column

Mar 20, 2014

I'm working on a query that is asking to return data on dependents which a person can have 0-many, in a single row but sep columns. The dependent data I need to include are Dep First Name, Dep Last Name, Dep Relationship.

So my result should look something like this:

EEID| DepFirstName| DepLastName| DepRelationship| DepFirstName| DepLastName| DepRelationship
121 Billy Larson Spouse Alison Larson Child

How do I do this with SQL?

View 1 Replies View Related

Dataset Store Procedure Return Values

Apr 1, 2008



Hi All,
I have written a stored procedure that has a return value (OUTPUT Parameter) and was wondering if there is any way to retreive this value in SQL Server Reporting Services 2005? I get the result fine, but cannot figure out how to get the return parameter.

Thanks in advance.

Glenn

View 5 Replies View Related

Using Stuff To Return A String Of Values From A Column Without A Cursor

Dec 13, 2007

I think it was Pat Phelan who posted a little trick here where he used the STUFF function to create a string fo values from a column without using a cursor.

I am starting a brand new project and I did my table design and I am awaiting a finalized requirements document to start coding and I thought I would spend a little time writing some code to autogenerate some generic one record at a time SELECT, INSERT,UPDATE and DELETE stored procedures. With the coming holiday things are getting quiet around here.

The code that is not working is listed below. It does not work. It returns Null. I suck.

DECLARE @column_names varchar(8000)

SET @column_names = ''

SELECT @column_names = STUFF(@column_names,LEN(@column_names),0,C.COLUMN_ NAME + ', ')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = 'MyTable'

SELECT @column_names

little help?

View 6 Replies View Related

Return Stored Procudure Values

Nov 5, 2005

I have a quick question for you about SQL stored procedures. If I'min a stored procedure and want to call another stored procedure andreturn values from the second stored procedure what is the procedure?I know you do the following to run the second stored procedure and passin any parameters:EXEC GetAuthorBooks @AuthorIDSo if I wanted the GetAuthorBooks to return all the books for an authorand then populate a temp table in the original stored procedure, how doI return those records, and populate the temp table?What do I have to add to this line: EXEC GetAuthorBooks @AuthorID?

View 4 Replies View Related

#Table Not Found Error Using Stored Procedure For DataSet

Aug 29, 2007

Hi all

I have a procedure where I am inserting some elements into #Table and then finally get the datset I need.
Now when I am using this procedure as dataset to my report, it throws up the following error:





Invalid object Name "#TEMP2".

The data that I retrieve is similar to the data that I get from this query in the post by Manivannan.D.Sekaran
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1871478&SiteID=1

Is it because my columns are generated on the fly that I am not able to retrieve the column headers appropriately. If so can someone suggest a way over to this?

I am not sure should I posting it here or in T-SQL Forum.








View 17 Replies View Related

Return Distinct Values From Stored Procedures

Aug 17, 2006

I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!

View 3 Replies View Related

Multiple Return Values From Stored Procdure.

Feb 1, 2007

I have a table that contains 5 columns (VarChar); where column(0) is a unique ID. Using the unique ID I would like to get the other 4 columns return to me via a stored procedure. Is it possible to have a sproc that has one input var and 4 output?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved