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

How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH And Cursor?
Dear All
 
I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.
 
Here is my query sample for yours to understand.
 
 

SET NOCOUNT ON

DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000)

Set @qstring = 'select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union 
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm'

PRINT '-------- ITEM Products Report --------'

DECLARE ITEM_cursor CURSOR FOR

execute (@qstring)

OPEN ITEM_cursor

FETCH NEXT FROM ITEM_cursor

INTO @ITEMCODE

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT ' '

SELECT @message = '----- Products From ITEM: ' +

@ITEMNAME

PRINT @message

-- Get the next ITEM.

FETCH NEXT FROM ITEM_cursor

INTO @ITEMcode

END

CLOSE ITEM_cursor

DEALLOCATE ITEM_cursor

 
Why i use @qstring? It is because the query will be changed by different critiera.
 
Regards
Edmund

View Replies !

---

Possible To Return A String Of Table Values?
This is a portion of my stored procedure in T-SQL

CREATE PROCEDURE GetPoll
(
@PollID INT,
@PollQuestion VARCHAR(1000) Output,
@PollOptions VARCHAR(1000) Output,
@PollPercentages DECIMAL Output
)

AS

SELECT @PollQuestion = (SELECT PollQuestion FROM Polls WHERE PollID = @PollID)

SELECT @PollOptions = (SELECT [Option] FROM PollOPtions WHERE PollID = @PollID)


*SELECT @PollPercentages = (SELECT [Option], COUNT([Option]) As Num_Votes FROM Votes WHERE PollID = 1 GROUP BY [Option])


The final part(*) of this SQL will return more than one value, so is it possible for me to return all that information in one varaible with one SELECT query?? Or would I need to use a loop and query a value at a time, and store it into a delimited string?

thx in advance...

View Replies !

---

Choosing Between Two Column Values To Return As Single Column Value
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 Replies !

---

Return Only Numeric Characters From A String Column
I have written this query to return only numeric characters from a string.
 
select *
from TableA
where isNumeric(Column_A) = 1
 
But I have discovered that the following
 
SELECT IsNumeric('-')
SELECT IsNumeric('£')
SELECT IsNumeric('$')
SELECT IsNumeric('+')
 
all return a value of 1. I do not want these in my result set.
 
If I use Column_A NOT LIKE '%[a-z]%' and Column_A <> '' I get characters such as "", ----- etc in my result set.
 
I would like a simple way of only returning numbers in my resultset.
 
Thanks for looking at this.
 
 

View Replies !

---

SQL CE 3.1 Connection String And Some Other Basic Stuff
Since I couldnt find an easy way to connect Orcas Beta 2 with SQL CE 3.1

I'll try by a connection string

since im totally new at this
does anyone have any idea how to get started?

Thxs!

View Replies !

---

Return Column Names Where Values Were Not Found [stored Procedure]
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 Replies !

---

Problem Concatenating Column Values Into String...
I have a customer who has recently migrated their SQL server to a newserver. In doing so, a portion of a stored procedure has stoppedworking. The code snippet is below:declare @Prefixes varchar(8000),declare @StationID int-- ...select @Prefixes = ''select @Prefixes = @Prefixes + Prefix + '|||'from Devicewhere Station_ID = @StationIDEssentially, we are trying to triple-pipe delimit all the deviceprefixes located at a specified station. This code has workedflawlessly for the last 10 months, but when the database was restoredon the new server, @Prefixes only contains the prefix for the lastdevice.Is there a server, database, or connection option that permits this towork that I am not aware of? Why would this work on the old server andnot on the new? (BTW - both servers are running SQL 2000 StandardSP4).Thanks!

View Replies !

---

Is There Any Solution For Dispalying String And Percentage Values In Single Column
i have a report with contains preview of percentage columns example of percentage of student marks in perticular subject like 95%. and if suppose any student not attend any test i have to dispaly like not attended statement.
 
 
 
so i have display two fields like 95% and not attended statement in same column, i given Cstr(Fields!Data.Value), it gives two fields with contains not attended statement of perticular query and it dispalys 0.95 % . but i need 95% and  not attended statement for  perticular query in same column.
 
 
is there any solution for dispalying string and percentage values in single column for given perticular query and those two values are disply  same  result compare with  preview at the time  of export to  excel sheet

 
 
plese send solutions ASAP
 
Thanks
 
James vs

View Replies !

---

Find In String And Return Part Of String
I am trying to find a way to find a certian character in a string and then select everything after that character.

for example i would look for the position of the underscore and then need to return everthing after it so in this case

yes_no

i would return no

View Replies !

---

Can You Return A Cursor From A Proc?
Can I do something like


CREATE PROCEDURE ProcA
AS

DECLARE @temp CURSOR
BEGIN
EXECUTE ProcB @temp OUT

END

GO

I am sure that this code is not right. But, if someone can tell me a better way to solve this, I would apprciate it.

AB

View Replies !

---

Employing XML Formatted String Data Rather Than Normal String(char(), Nchar() Or Varchar() Values
Hello,
Is there a way of passing in an xml formatted string or text to the report through a data set and have the textbox or table in which it displays keep the formatting specified in the xml string rather than in the textbox properties?
 
Thanks.

View Replies !

---

How To Return Multiple Results In 1 Fetch Cursor- Urgent Help!
I want to send 1 email with all clientname records which the cursor gets for me.
My code however is sending 1 email for 1 record i.e clientname got from db. What's wrong? please help.
I ano table to understand here about the while if right.
thanks.
+++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE test1
AS

declare @clientName varchar(1000)


declare myCursor CURSOR STATIC
for


select client_name
from clients
-------------------------
-- now prepare and send out the e-mails
declare @ToEmail varchar(255)
declare @FromEmail varchar(255)
declare @Subject varchar(255)
declare @Body varchar(2000)
declare @UserID numeric(38)
declare @UserName varchar(255)
declare @SMTPServer varchar(100)
set @SMTPServer = 'test.testserver.com'

-- loop for each record

open myCursor
fetch next from myCursor
into @clientName

--loop now:
while (@@fetch_status=0)

begin -- while(@@fetch_status=0)
-- check if valid "To" e-mail address was found
if ((@clientName is null) or (ltrim(@clientName) = ''))

begin
--should not come here anytime ideally
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = 'was emailed to wrong person'
set @Body = 'the client name got is : '+ @clientName + 'client is null or empty'

end --if

else

begin
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = '-testing'
set @Body =

'this will send
ClientName:'+ @clientName
end --end else

-- send the e-mail
--exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
--fetch next from myCursor into @clientName

fetch next from myCursor
into @clientName

end --while(@@fetch_status=0)
exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
close myCursor
deallocate myCursor


GO

View Replies !

---

How Do I Concatenate Different Values For The Same Primary Key Without Using Cursor..
Can I avoid cursor (use while of if statement instead ) if for example I have two sales people for the same company and I need one row per company?

View Replies !

---

Declare Dynamic Cursor From String
Hi,is it possible to create a cursor from a dynamic string?Like:DECLARE @cursor nvarchar(1000)SET @cursor = N'SELECT product.product_idFROM product WHERE fund_amt > 0'DECLARE ic_uv_cursor CURSOR FOR @cursorinstead of using this--SELECT product.product_id--FROM product WHERE fund_amt > 0 -- AND mpc_product.status= 'aktiv'Havn't found anything in the net...Thanks,Peppi

View Replies !

---

How To Loop A Cursor And Accumulate A String Value ?
Hi
Why can't I loop a cursor and add values to a string  in Sql server ?



Code Block
ALTER FUNCTION [dbo].[fn_Get_Project_String]
()
RETURNS nvarchar(255)
AS
BEGIN
DECLARE @Prosjekt nvarchar(255), @Pro nvarchar(255)
 
DECLARE c1 CURSOR
FOR select nvarchar3
FROM dbo.AllUserDataCopy
 
OPEN c1
FETCH NEXT FROM c1 INTO @Pro
WHILE @@FETCH_STATUS = 0
 
BEGIN
select @Prosjekt = '<item>' + @Pro + '</item>' + ''
FETCH NEXT FROM c1 INTO @Pro
select @Prosjekt = @Prosjekt + @Pro
END
 
CLOSE c1
DEALLOCATE c1

RETURN @Prosjekt
END
 
 


 
Ivar

 
 

View Replies !

---

Save Me From A CURSOR. Flag Values That Won't Convert.
Hey all:
 
Right now I have a cursor that makes me want to puke.  This is the last cursor in my current project and I want to replace it with a much faster set based operation.
 
Here is the problem.  I have a table with say 1-3 million records.  There are fields that get loaded in with date information.  These fields are varchar because the date information could very well be mangled data that needs to be reviewed by a user.  What I need is to go through these varchar fields and flag the values that cannot convert to smalldatetime.
 
I have another table that houses the primary key and the field of the record that cannot convert.
 
Essentially, I have a series of filters that run and flag using set based stored procedures.  If there is a record that gets through that contains a value that cannot be converted, I have a cursor that steps through the data and attempts to convert the value.  If it is able to be converted, then it continues on until it finds the value that is holding up the conversion.
 
I guess if I can run a query that will return all records that can convert for the field (or can't convert)  I'd be all set.  Any help here is appreciated.
 
--Thanks--
 

View Replies !

---

Return Values?
Using ado, the _CommandPtr::Execute method returns a recordset. When calling a storedproc with this method, does the 'return' statement return the value in a recordset ?

EG:

//c++ ADO
_CommandPtr pCmd(__uuidof(Command));
............
_RecordsetPtr pRs = pCmd->Execute(NULL, NULL, adCmdStoredProc);

// Stored proc
CREATE PROCEDURE usp_my_proc
AS
SELECT .... FROM tbMyTable WHERE x = something
IF(@@ERROR <> 0)
return -1
.....


What in the case of an error would be returned to pRs recordset?
Any thoughts?

Mike B

View Replies !

---

Return Values In SP
hello,
is it possible to return a return value as a numeric datatype

I can successfully return as an integer, but when i change it to numeric i get this error

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]Invalid scale value
/output.asp, line 15

my output value in my asp code is thus

Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 131, 4)

thanks
Afrika

View Replies !

---

Return Values In SP
hello,
is it possible to return a return value as a numeric datatype

I can successfully return as an integer, but when i change it to numeric i get this error

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]Invalid scale value
/output.asp, line 15

my output value in my asp code is thus

Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 131, 4)



thanks
Afrika

View Replies !

---

SQL Return Values In ASP
How do you get the return value(in ASP) of @@identity in a stored procedure while inserting a row?

View Replies !

---

Return Into One String
I have a table with a column called AccessTypes which contains a singleletter.I want to return these accesstypes from a query into one string. e.g.if there were 3 entries of A, S and DI want to select them and instead of returning 3 rows, I want just 1string like "ASD"can it be done?

View Replies !

---

Return Dat In String
hi! how would i return the current day in string format?

the output should be sun,mon,tue,wed,thu.. etc?

pls. help.. thanks

View Replies !

---

Multiple Return Values
I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000)

Right now, I use the statement "return @@Identity" for a single value, but there is another variable assigned in the procedure, @NewCounselingRecordID that I need to pass back to the calling class method.

I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter.

However, I feel this solution is kludgy. Is there a more correct way to accomplish this?

Thanks in advance for your comments.

View Replies !

---

Catching Return Values Of A SP
I have calling a stored procedure that returns two values, and I want to catch these values and to store them into a variable.


Here is a piece of my SP inside SQL Server that shows the returned values:

…
SELECT @Id = SCOPE_IDENTITY()
SELECT @Id AS user_id
SELECT 1 AS Value
END
GO



In my aspx page I am trying to call the first value like this:

Dim nID
CmdInsert.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.bigint, 8, "user_id"))
CmdInsert.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters("@RETURN_VALUE").Value = nID


And to check if the right value is returned I use:

strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = nID
strConnection.close()



But now no value appears in the textbox, How can I achieve it? What is wrong?

View Replies !

---

Return Values From DTS Wizard
I want to launch the DTS wizard through a C# application. Myapplication simply launches the wizard using Process.Start() and thenwaits for the process to finish. When the control comes back to myform, from where DTS wizard was launched, all I need to know is whetherthe wizard was finished or whether the user pressed the cancel buttonon the wizard. Is there any way to know this info?TIA

View Replies !

---

READTEXT And Return Values
First question :

How can I set the return value of READTEXT to a variable of type nvarchar.

Second question :


I have a table t1 with a ntext column n1.

The ntext column has has words separated by empty space.
Each word can be assumed to be of size <= 255 characters.

How can I extract all the keywords in the ntext column to a table t2 with a column word nvarchar(255).

Assume that the text in column n1 is big enough so that it cannot be cast into a nvarchar or any other simpler type.



Any help on this is greatly appreciated.
Please do provide a sample code.

Alok.

View Replies !

---

Access To Return Values Of #Name?
What causes Access to return values of #Name? in every data column when select from SQL attached tables. This is not consistant. ??

View Replies !

---

Return DISTINCT Values
Hi,
 
How do I ensure that DISTINCT values of r.GPositionID are returned from the below??
 


Code Snippet
SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType
FROM @GResults r
LEFT OUTER JOIN
ReconComments cm
ON cm.GPositionID = r.GPositionID
WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)
ORDER BY GCustodian, GCustodianAccount, GAssetType;
 
 


Thanks.

View Replies !

---

Select Does Not Return Values
I am new to SQL and I am trouble-shooting a problem with a home-grown app someone else wrote using PERL. It has a web interface with names of boards. I found the program where i need to add the board names into and did that. The new board names show up in the drop-down list in the Web page for the app. Alerts are sent to the new board names and show up on the new boards. Users are granted access to the new boards and they can clear items off the new boards. Yet when i try to use a report function for the new boards i added, nothing is returned. I even ran a simple SQl select statement specifying the new board names and nothing is returned. The older board names, some of which i added myself, return values. I don't know what is going on. Any help is appreciated.

View Replies !

---

Return Values Like Other Values
Hi,

I have two tables. Both tables have a FirstName and a LastName field. I can return the results where (LastName = LastName) and (FirstName = FirstName). However in one table some of the FirstName values are shortened ie Pat is P etc.
Is there some way (its prob using LIKE but i can't get it right) I can write the below query so that it returns values where the first letter of FirstName in one table equals the first letter of FirstName in the second table.


SELECT tbl2.FirstName, tbl2.LastName, SALARY.[Employee No]
FROM SALARY INNER JOIN tbl1 ON (SALARY.LastName = tbl2.LastName) AND (SALARY.FirstName = tbl2.FirstName)

Cheers for all help.

View Replies !

---

Sqldatasource Return Into A String
Hi..
how can i insert the sqldatasource return into a string
 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>" SelectCommand="SELECT COUNT(productID) FROM CSK_Store_Product"></asp:SqlDataSource>
this consults return a number of elements in the table, i need insert this number into a string
Thanks...

View Replies !

---

Return Part Of A String
how can you return part of a string and convert it into an integer value? Maybe like this:

convert(left(column, 3) as int)

does that work?

The Yak Village Idiot

View Replies !

---

Inserting Parsed String Using Cursor,want To Validate Line Before Insert
Hi ,
We loading file using DTS into ''working" table with 1 column then open cursor and
parse each line into 50 columns and insert each line into
table CustomerOrders

We start to get Files with invalid lines.

Insert of line cause error
---
Server: Msg 8115, Level 16, State 8, Line 202
Arithmetic overflow error converting numeric to data type numeric.
---


Since code inside procedure ,failure returned by procedure .

I want to verify column in cursor against column in table CustomerOrders and if any column in cursor invalid insert row as text in log table and continue


Is possible to do it without testing each column (have 50 columns!!!) against every possible scenario

Thanks
Alex

View Replies !

---

‘default To Local Cursor’ Causes Errors In String-built Declares
Hi,

I would like to use the dboption ‘default to local cursor’ to true.

But when I set it to true for my database, cursors that are built in a ‘string’ and then executed return an error :

Select @SQL = 'Declare SICCursor cursor For Select SIC1 From ' + @StateTable + ' Where BusinessName = ' + '''' + @BusinessName + '''' + ' Order By PubDate desc'

Exec(@SQL)

Open SICCursor

Openning the cursor returns the error message

“A cursor with the name 'SICCursor' does not exist.”

What am I doing wrong (cursors declared ‘normally’ do not have a problem)?

Thanks,
Judith

View Replies !

---

How Can I Force The Query To Return Values?
I want the following query to return a row even when table 'X' is empty. How would I do this?
SELECT TOP 1 @Var1, @Var2, @Var3 from X
The parameters @Var1, @Var2 and @Var3 are passed to the stored procedure in which the above query is included.
When table is empty, it reurn nothing. It only return a row when table is not empty.

View Replies !

---

Return Output Values From View
How can I return values from my SQL view back into my aspx page and shove each returned value into variables I define for certain returned fields?

View Replies !

---

Return Values With Stored Procedure
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 Replies !

---

Return Stored Procudure Values
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 Replies !

---

SQL Query Return Unused Values
I have a table. In the table I have different types of mailingaddresses. The addresses are linked by and ID. ex,addresses per ID 1ID address1 add21 add31 add4distinct Addresses in tableID address1 add21 add31 add4What I am trying to figure out is if this is possible. Is it possibleto figure out some how with a query that in the above example ID 1is missing add1. I am trying to populate a dropdown list in my codewith just the addresses that are left from the distinct list. I trieddoing an intersect something like this but that did not seem to work.SELECT typeFROM address_demoINTERSECTSELECT typeFROM address_demo AS address_demo_1WHERE progid = '12954892'I am wondering if I am moving in the right direction or if this is evenpossible......Thanks

View Replies !

---

Select 3 Values From One Table To Return In One Rs
I need to access a table and return 3 values from it in the samerecordset - ie one iteration of the recordset will have 3 values fromthe same database, I have looked at sub queries but they dont seem tobe able to do what i want.I would be grateful for any guidanceS

View Replies !

---

Select 3 Values From One Table To Return In One Rs
I am having trouble accessing a table and am seeking out some adviseon how to do it.I have a select statement that joins several tables and everythingworks ok , however I want to access a further table to retrieve 3 rsvalues, so basically I want to retrieve 3 values from the same table -identifying each by a value in that table.Any help or pointer would be very welcome.Simon ChristiestrSearch is a dynamically created search stringCopy of select statement belowsql = "SELECT Master.Master_Key,Master.First_App,Original_Info_D ate,Last_Info_Date,Area.Area_NameAS Area_Name, State_Cntry.State_Cntry_Name, Company.Oper_Name1 ASOper_Name1, Company.Oper_Name2 AS Oper_Name2, " _& " Company.Oper_Name3 AS Oper_Name3, Location.City AS City,Projects.Project_Name AS Project_Name, " _& " Expansion.Expansion_Code AS Expansion_Code,Master.Cap_Size AS cap_size, Cap.Cap_Unit AS Cap_Unit, Master.Cost ASCost, " _& " Master.Status AS Status, Master.Estimated_Year_CompletedAS Estimated_Year_Completed " _& " FROM Master INNER JOIN " _& " Area ON Master.Area_Key = Area.Area_Key INNER JOIN " _& " Company ON Master.Company_Key = Company.Company_Key INNERJOIN " _& " Location ON Master.Location_Key = Location.Location_KeyINNER JOIN " _& " Expansion ON Master.Expansion_Key =Expansion.Expansion_Key INNER JOIN " _& " Cap ON Master.Cap_Key = Cap.Cap_Key INNER JOIN " _& " Projects ON Master.Project_Key = Projects.Project_KeyINNER JOIN " _& " State_Cntry ON Location.State_Cntry_Key =State_Cntry.State_Cntry_Key " _& " WHERE " & strSearch _& " ORDER BY Area.Area_Key, State_Cntry_Name,Oper_Name1,City"The table I need to access is called LECLEC_Key (PK)Master_Key - Database Master KeyCompany_Key - matches the companyLEC_Type_Key - 3 types per company (values L,E or C)Each Master Key will have 3 values in the table, one each of L,E and CHope this makes some sort of senseThanks in advanceSimon Christie

View Replies !

---

Select 3 Values From One Table To Return In One Rs
strSearch is a dynamically created search stringCopy of select statement belowsql = "SELECTMaster.Master_Key,Master.First_App,Original_Info_D ate,Last_Info_Date,Area.Area_NameAS Area_Name, State_Cntry.State_Cntry_Name, Company.Oper_Name1 ASOper_Name1, Company.Oper_Name2 AS Oper_Name2, " _& " Company.Oper_Name3 AS Oper_Name3, Location.City AS City,Projects.Project_Name AS Project_Name, " _& " Expansion.Expansion_Code AS Expansion_Code,Master.Cap_Size AS cap_size, Cap.Cap_Unit AS Cap_Unit, Master.Cost ASCost, " _& " Master.Status AS Status, Master.Estimated_Year_CompletedAS Estimated_Year_Completed " _& " FROM Master INNER JOIN " _& " Area ON Master.Area_Key = Area.Area_Key INNER JOIN " _& " Company ON Master.Company_Key = Company.Company_Key INNERJOIN " _& " Location ON Master.Location_Key = Location.Location_KeyINNER JOIN " _& " Expansion ON Master.Expansion_Key =Expansion.Expansion_Key INNER JOIN " _& " Cap ON Master.Cap_Key = Cap.Cap_Key INNER JOIN " _& " Projects ON Master.Project_Key = Projects.Project_KeyINNER JOIN " _& " State_Cntry ON Location.State_Cntry_Key =State_Cntry.State_Cntry_Key " _& " WHERE " & strSearch _& " ORDER BY Area.Area_Key, State_Cntry_Name,Oper_Name1,City"The table I need to access is called LECLEC_Key (PK)Master_Key - Database Master KeyCompany_Key - matches the companyLEC_Type_Key - 3 types per company (values L,E or C)Each Master Key will have 3 values in the table, one each of L,E and CHope this makes some sort of senseThanks in advanceSimon Christie

View Replies !

---

Return Multiple Values From A Function
searched all over, couldn't find a solid answer...is it possible to return multiple values from a sql function with sql server 2005?

e.g., I want to do this:

select id, data, whatever, dbo.fnMyFunction(id, whatever) from table

and have the output have columns (id, data, whatever, col1, col2) where col1 and col2 are returned by fnMyFunction

possible? easier way? thanks in advance...

View Replies !

---

Please Help... Stored PRocedure Return Values
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 Replies !

---

How-to Return Calculated Values From A Called SP
Can someone please reply w/ example syntax on how to receive calculated variables from an invoked SP. DESCRIPTION:

sp_caller invokes sp_calc_values (passing @var1, @var2) via:
exec sp_calc_values @var1, @var2

sp_calc_values receives @var1, @var2 then calculates @var3, @var4

HOW does sp_caller receive the calculated values @var3, @var4 ???

thx in advance

View Replies !

---

Return And Assigning Values In Functions
Hi ,

I will need some examples in assigning and getting values using SQLServer 2005. For eg. How can I store the value that I retrieved in a variable and return that value ? How can I use a function inside a stored procedure ? Do we have any examples  or some simple sample code just to take a look ?

For eg I have written the following function which I called from a stored procedure.
BEGIN
    --Declare the return variable here
    DECLARE @Rows NUMERIC(10)
    DECLARE @RETURN_ENABLED VARCHAR(1)
    -- Add the T-SQL statements to compute the return value here

     SELECT @Rows = MAX(PROFILE_INDEX) FROM PROFILE_PERMISSION PP
        INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
   WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
         not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
         PP.APPLICATION_CODE = @is_appl AND
         PP.MENU_NAME=@menu_name
    Group By Profile_INdex
   
    IF @Rows > 0
        SELECT @RETURN_ENABLED = 'N'
    ELSE
        SELECT @RETURN_ENABLED = 'Y';
   

    -- Return the result of the function
    RETURN @RETURN_ENABLED;

END

Is it correct ? The variable @ROWS will be assigned with the values that the sql statement will return ?

From the stored procedure I'm calling the function inside a CTE.

;WITH GetHierarchy (item_text ,orden , read_order,  item_parent , menu_item , enabled)
    AS
    (--Anchor.
        select tb1.item_text, tb1.orden, tb1.read_order, tb1.item_parent , tb1.menu_item ,
        dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled
        From sys_menu_item as tb1
        where tb1.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb1.item_parent not in ('m_toolbar','m_window','m_help')
            And tb1.item_parent= @menu_name
    --Members
        UNION ALL
        select tb2.item_text, tb2.orden, tb2.read_order,  tb2.item_parent , tb2.menu_item ,
        dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled
           from sys_menu_item as tb2 , GetHierarchy
        where  tb2.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb2.item_parent not in ('m_toolbar','m_window','m_help')
        And tb2.item_parent = GetHierarchy.menu_item and tb2.menu_name = @menu_name    
    )
    select Space(5*(orden)) + item_text as menui, orden, read_order, item_parent , menu_item ,enabled
    From GetHierarchy

Am I doing it correctly ?

I would appreciated any help you could give me.

Thank you

View Replies !

---

Help With A Join To Return Unique Values
Hi All,

I need a bit of help with a join. I have 2 tables :

TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue

Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue

TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID


and what I want to retreive is :


For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID

Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID

thanks in advance

View Replies !

---

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

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 Replies !

---

Function With Expression To Return Values
I have created a function to return values, which works fine, but I can't do calculations in it.

CREATE FUNCTION [dbo].[tf_Asset_Portfolio](@deal_id int,
@as_of_date datetime)
RETURNS TABLE
AS
RETURN ( SELECT DISTINCT dbo.Assets.issue_id, SUM(DISTINCT dbo.Assets.par_amount) AS par_amount, SUM(DISTINCT dbo.Assets.par_amount) AS market_value
FROM dbo.Issue INNER JOIN
dbo.Assets ON dbo.Issue.issue_id = dbo.Assets.issue_id INNER JOIN
dbo.Issuer_Rating_History ON dbo.Issue.issuer_id = dbo.Issuer_Rating_History.issuer_id
WHERE (dbo.Issuer_Rating_History.as_of_date <= @as_of_date)
GROUP BY ALL dbo.Assets.issue_id, dbo.Assets.deal_id, dbo.Issue.default_date
HAVING (dbo.Assets.deal_id = @deal_id) )

I need to do calculations on market value based on the default date.
If default date isn't specified then it should be 100% of par amount.
If default date is less than one year ago - 65% of the par_amount.
If default date is one or more years ago - 0.
I have no idea about how to do this and everything I try wont work.
I created another function to do the calculations and this seems to work, but it only does one record instead of all of them.

CREATE FUNCTION dbo.tf_Asset_Portfolio2
(@deal_id int,
@as_of_date datetime)
RETURNS @Market TABLE
(issue_id int, par_amount money, market_value money)
AS
BEGIN
DECLARE @ReturnDate datetime
DECLARE @DD datetime
DECLARE @PA money
DECLARE @MV money
DECLARE @ID int
DECLARE @DateD int

SELECT TOP 1
@ReturnDate = LAST_BATCH
FROM master..sysprocesses
WHERE SPId = @@SPID

SELECT @ID = issue_id FROM Assets WHERE Assets.deal_id = @deal_id
SELECT @PA = SUM(DISTINCT par_amount) FROM Assets WHERE Assets.issue_id = @ID AND Assets.deal_id = @deal_id
SELECT @DD = default_date FROM Issue WHERE Issue.issue_id = @ID

SET @DateD = DateDiff("yyyy", @DD, @ReturnDate)

If @DD = Null
BEGIN
SET @MV = @PA
END
Else If @DD > @ReturnDate
BEGIN
SET @MV = @PA
END
Else If @DateD < 1
BEGIN
SET @MV = @PA * .65
END
Else If @DateD >= 1
BEGIN
SET @MV = 0
END

insert into @Market
(issue_id, par_amount, market_value)
values
(@ID,@PA,@MV)

RETURN
END

I need to combine the functionality of being able to return mutliple records that isn't in the 2nd function and being able to calculate the market value which isn't in the first one. Please help. Thank you in advance.

View Replies !

---