I'm trying to execute a parameterized SQL string but need a return parameter (a multiplier) to include in a later SELECT statement.
What i'm looking for is equivalent to this paraphrased statement:
EXEC('SELECT @val = from @column where Value = @Value')
Where I would later use @val something like:
Select Value * @val as Total Value
Is there an easy way to do this..i've read and read to no avail.
TIA,
Charles
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
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
Is there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
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
I need to get a value from another Stored Procedure to use within another Stored Procedure. This is what I currently have, but it is not even close, I'm sure:
CREATE PROCEDURE dbo.sp_JT_BS01c_Calendar_Build_BufferSheet_DateRange AS
DECLARE @MinOfDMD_WK datetime, @MaxOfDMD_WK datetime
@MinOfDMD_WK = Exec sp_JT_BS01a_CalendarBuild_Min_PlanningWeek
@MaxOfDMD_WK = Exec sp_JT_BS01b_CalendarBuild_Max_PlanningWeek
SELECT @MinOfDMD_WK, [master - weekly_range].week,
@MaxOfDMD_WK, dbo.fn_Bucket_Range([week],@MinOfDMD_WK,@MaxOfDMD_WK) AS [Inclusion Range]
FROM [master - weekly_range], @MaxOfDMD_WK, @MinOfDMD_WK
WHERE dbo.fnBucket_Range([week],@MinOfDMD_WK,@MaxOfDMD_WK)=1;
Thanks,
Bob Larson
This is what I am trying:
set @cntsql = 'select count(' + @dimky + ') as dimcnt from ' + @dimtb + ' where ' + @dimky +' is not null'
set @dimcnt = execute sp_executesql @cntsql
What I want to do is return the count from the dynamically selected database. If I type it in like:
set @dimcnt = (select count('ptky') as dimcnt from pttable where ptky is not null)
it works.... can anyone help... this code is in the middle of a cursor.
Hi,
How can I check return value from
EXECUTE sp_executesql(SQLStr)
Thanks,
ok I have a stored procedure in my MS-SQL Server database.
It looks something like this.....
CREATE PROCEDURE updatePCPartsList
(
@Descriptionvarchar(255),
@ManCodevarchar(255),
@ProdCodevarchar(255),
@Pricedecimal(6,2),
@Commentsvarchar(255)
)
AS
declare @IDFound bigint
declare @LastChangedDate datetime
select @LastChangedDate = GetDate()
select @IDFound = PK_ID from PCPartsList where ProdCode = @ProdCode
if @IDFound > 0
begin
update PCPartsList set Description = @Description, ManCode = @ManCode, ProdCode = @ProdCode, Price = @Price, Comments = @Comments, LastChanged = @LastChangedDate where PK_ID = @IDFound
end
else
insert into PCPartsList (Description, ManCode, ProdCode, Price, Comments, LastChanged) values(@Description, @ManCode, @ProdCode, @Price, @Comments, @LastChangedDate)
GO
It executes fine so I know i've done that much right....
But what i'd like to know is how I can then return a value - specifically @LastDateChanged variable
I think this is a case of i've done the hard part but i'm stuck on the simple part - but i'm very slowly dragging my way through learning SQL.
Someone help?
Hi,
How can I check return value from
EXECUTE sp_executesql(SQLString)
Thanks,
Hi All,
Create proc sproc_Insert
@TableName varchar(50),
@InsertColumns varchar(1000),
@InsertValues varchar(2000),
@WhereCondition varchar(200)
as
Begin
Declare @CheckStr nVarchar(2000)
Declare @RetVal int
Set @checkStr = 'Select * from '+ @TableName + ' '+ @WhereCondition
execute sp_executesql @checkStr,@RetVal output
print @RetVal
End
I am not able to retrieve the return value in the above procedure. For example if data exists then 1 else o
Thanks & Regards
Bijay
Can anyone tell me how to capture the return code of a process launched by an Execute Process Task? I am able to capture the output by using the StandardOutputVariable but can't seem to capture the actual code.
View 4 Replies View Related
I am trying to have an Excecute SQL Task return a single row result set executed on SQL Server 2005.
The query in the Execute SQL Task is:
select 735.234, 2454.123
I get a conversion error when trying to assign to SSIS variables of type Double.
I have nothing configured in the "Parameter Mapping" tab.
I have the two SSIS Double variables mapped to the Tesult Name 0 and 1 in the "Result Set" tab
I don't want to use a for loop enumerator since there is a single row returned.
I simply want to assign these two values to SSIS Double variables (double is the closest match)
I can't even hack this by converting the decimals as string and then using DirectCast to convert them to Double.
Thanks for the help
Hi Guys, I can't for the life of me get the output from the stored procedure into my variable in VB.net. Can you guys help out, I'm on my third hour. Trying to learn this peice as I go (output parameters). I have truncated the code to look at the output parameter, everything works except the VB side of the output parameter, my messagebox keeps giving me a value of zero. I have even tried .SQLValue instead of .Value and it didn't work...thoughts????Thanks in advance!Tim
SQL:ALTER PROCEDURE dbo.SDC_RisIncidentInsert @SeqInc int output ASSelect @SeqInc = Max(Sequence) + 1 From _Smdba_._Telmaste_Select @SeqInc---------------------------------------------------------------------------------------------
VB.NetPrivate Sub InsertIncident()Dim conn As SqlConnectionDim comm As SqlCommandDim connectionstring As String = ConfigurationManager.ConnectionStrings("Magic").ConnectionStringconn = New SqlConnection(connectionstring)comm = New SqlCommand("SDC_RisIncidentInsert", conn)comm.CommandType = System.Data.CommandType.StoredProcedurecomm.Parameters.Add("@SeqInc", SqlDbType.Int)comm.Parameters("@SeqInc").Direction = ParameterDirection.Output
Dim SeqIncident As Integer = comm.Parameters.Item("@SeqInc").ValueMsgBox(SeqIncident)
Try conn.Open() comm.ExecuteNonQuery()Catch ex As Exception dberrorlabel.Text = "Could not Insert Incident Record"
Finally conn.Close() Response.Redirect("/RISDEV/SDC_Incident.aspx?Name=" & SeqIncident & "#Incident")End TryEnd Sub
hi! how would i return the current day in string format?
the output should be sun,mon,tue,wed,thu.. etc?
pls. help.. thanks
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 3 Replies View Related In my SP I have an output parameter,
the SP work good,
but in the command parameter I alwase get null value/
here is my code:
and in the profiler I see that the query work and return the value, here the query that I copy from the profiler:
thanks!
I am trying to use dynamic sql with a return parameter, but with limited success. I am using WebMatrix, vb.net and MSDE to perform this routine. Can someone please clue me in. I have read two fine articles by <a href='http://www.algonet.se/~sommar/dyn-search.html>Erland Sommarskog</a> on dynamic sql using sp_executesql, as well as the somewhat opaque article by Microsoft (262499) on the subject.
While there may be other ways to accomplish this task, I am interested in making it work with dynamic SQL. In production, there will be over 20 parameters coming from the vb.net to the SQL, being driven from user input. Then those same variables will be used to actually retrieve the records to a datagrid.
So with a tip of the cap to Rod Serling, I submit this small code and SQL for your consideration from my Twilight Zone:
Public Function totalrecordsbysql(list as arraylist) as integer
dim RetVal as new integer
dim querystring as string
Dim cn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Indiafriend"))
Dim cmd As SqlCommand = New SqlCommand("SimpleDynProfileCount", cn)
cmd.commandtype = commandtype.storedprocedure
dim mydr as SqlDataReader
cmd.Parameters.add("@TotalRecords",SqlDbType.int).direction=ParameterDirection.Output
cmd.Parameters.add("@age",sqldbtype.int).value = 18
cn.Open()
try
mydr=cmd.executereader()
catch e as sqlexception
dim err as sqlerror
dim strErrorString as string
for each err in e.Errors
strErrorString += "SqlError: #" & err.Number.ToString () & vbCRLF + err.Message
trace.write("sqlexception",strErrorString)
Next
finally
RetVal = cmd.parameters("@TotalRecords").value
end try
Return RetVal
cn.close()
End Function
Now here is the stored procedure:
CREATE PROCEDURE SimpleDynProfileCount
@age int,
@TotalRecords int output
AS
Declare @sql nvarchar(4000),
@paramlist nvarchar(4000)
select @sql = 'select @xTotalRecords = count(*) from profile where 1 = 1 '
// RAISERROR(@sql, 16, 1)
IF @age > 0
Select @sql = @sql + ' AND age > @xage '
Select @paramlist = '@xage int, @xTotalRecords int output'
Execute sp_executesql @sql,@paramlist,@age,@xTotalRecords = @TotalRecords output
select @TotalRecords
GO
Please note the commented RAISERROR statement. If I uncomment this statement, I will get a return value of 11 records. If I leave it out, I get zero records.
The data is the database should return 11 records, based on the criteria of age > 11
I'm having a terrible time getting a return value from my stored procedure. The sp sets the value and returns it correctly in the query analyzer, but I can't get the calling code - using VB in VS2003 - to read the value. I've tried putting it into an integer, and using a Return, and also putting it into a string, and using an output parameter. Either way: nada. What am I doing wrong????
Here's the sp:
CREATE PROCEDURE sp_HOUSRoomAudits @Result VarChar(1) OUTPUT, @Dorm VarChar(12), @Room VarChar(5), @StuID VarChar(14) AS
DECLARE @Capacity IntDECLARE @Assigned Int--DECLARE @Result VarChar(1)DECLARE @BD BIT
SET @BD=(SELECT Boarding FROM HOUS_StudentMaster WHERE StudentID=@StuID)
SET @Capacity=(SELECT [Student Capacity] FROM HOUS_Rooms WHERE Bldg=@Dorm AND [Room #]=@Room)
SET @Assigned=(SELECT COUNT(PSID) FROM HOUS_StudentMaster a JOIN HOUS_Dorms b ON a.Dormatory=b.Dormitory WHERE b.Bldg=@Dorm AND a.Status='Active' AND a.Room=@Room)
IF (@BD=1 AND @Room='N/A') OR (@BD=0 AND @Room<>'N/A')
SET @Result='3'ELSE IF @Assigned<@Capacity SET @Result='1' ELSE SET @Result='2'
print @Result--RETURN @ResultGO
Here's the calling code; I'm leaving out all the dim's etc down to the output parameter:
Dim Parm4 As SqlParameter = scmdReturn.Parameters.Add("@Result", SqlDbType.VarChar)
Parm4.Direction = ParameterDirection.Output
Parm4.Value = "0"
Try
scmdReturn.ExecuteNonQuery()
strOut = Parm4.Value
Catch ex As Exception
Me.lblProblem.Text = "Failed to execute command, err = " & ex.Message
Me.lblProblem.Visible = True
Finally
scnnReturn.Close()
End Try
Hoping you can help, and thanks!
Ka
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...
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
hi,
i have a question. is there a way to execute a string when there is a function in the string?
here is an example. (try to run this at query analyzer)
declare @teststring varchar(255)
declare @resultstring varchar(255)
set @teststring = 'getdate()'
select @resultstring = exec @teststring
usually i can just get the result by using
select @resultstring = getdate()
I'm attempting to return a value from my stored procedure. Here is my button click event that runs the stored procedure. protected void btn_Move_Click(object sender, EventArgs e)
{
/*variables need for the update of the old staff record
and the creation of the new staff record.
*/
BMSUser bmsUser = (BMSUser)Session["bmsUser"];
int staffid = Convert.ToInt32(bmsUser.CmsStaffID);
int oldStaffProfileID = Convert.ToInt32(Request.QueryString["profileid"]);
string newManager = Convert.ToString(RadComboBox_MangersbyOrg.Text);
int newMangerProfileID = Convert.ToInt32(RadComboBox_MangersbyOrg.Value);
SqlConnection conn = new SqlConnection(connect);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_BMS_MoveStaffMember";
cmd.Parameters.Add("@OldStaffProfileID", SqlDbType.Int);
cmd.Parameters["@OldStaffProfileID"].Precision = 9;
cmd.Parameters["@OldStaffProfileID"].Scale = 0;
cmd.Parameters["@OldStaffProfileID"].Value = oldStaffProfileID;
cmd.Parameters.Add("@NewManagerProfileID", SqlDbType.Int);
cmd.Parameters["@NewManagerProfileID"].Precision = 9;
cmd.Parameters["@NewManagerProfileID"].Scale = 0;
cmd.Parameters["@NewManagerProfileID"].Value = newMangerProfileID;
cmd.Parameters.Add("@LastModifiedBy", SqlDbType.Int);
cmd.Parameters["@LastModifiedBy"].Precision = 9;
cmd.Parameters["@LastModifiedBy"].Scale = 0;
cmd.Parameters["@LastModifiedBy"].Value = staffid;
SqlParameter sp = new SqlParameter("@OLDManagerReturn", SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp);
lbl_ERROR.Text = Convert.ToString(sp);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
conn.Close();
My parameter sp is coming back null for some reason. Am I not setting the parameter correctly? I set a break point and it says the value is null.
Here is the parts of my stored proc returning the value...
DECLARE @OLDManagerReturn numeric(9)
SELECT @OLDManagerReturn = ManagerProfileID FROM tblBMS_Staff_rel_Staff WHERE StaffProfileID = @OldStaffProfileID AND Active=1 AND BudgetYear = @BudgetYear
RETURN @OLDManagerReturn
I have a stored proc which will insert and update the table. In this stored procedure I have a output parameter called @rows which is by default 0. Now when ever I insert or update the table and it is successful, then the output parameter should be 1 i.e the out parameter should return value 1 or else default 0.
Here is my code:
ALTER PROCEDURE [dbo].[sample]
(
@TestVARCHAR(256),
@Created_by Nvarchar (256),
@name nvarchar (100),
@rows int=0 output
)
[Code] ....
I'm having a hard time locating a listing of potential error codes and the meanings for the Execute Process Task component...can someone assist?
Thanks
Hello,
How can I pass the value of the count(*) to outside the execute. I need the value to continue with the sp
set @sql= 'declare @res int select @res=count(*) from t
where tam=(select '+@posxx+' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007+')'
???????? set @result=exec (@sql) ????????????? Something like this
Thanks
Paulo
I am trying to create an Execute SQL task that sets a variable.
This is my SQL
DECLARE @Period AS DATETIME
SET @Period =Parameter0 + '/01/' + Parameter1
SET @Period = DATEADD(m, -1, @Period)
SELECT DATEADD(s, -1, @Period)
This statement parses okay.
I mapped two variables called "User::PeriodMonth" and "User::PeriodYear" in the Parameter Mapping tab to the parameters.
In the Result Set tabl I have mapped a variable "User::PeriodStartDate" to Result Name "PeriodStartDate".
The error I get is the following:
[Execute SQL Task] Error: Executing the query "DECLARE @Period AS DATETIME SET @Period =Parameter0 + '/01/' + Parameter1 SET @Period = DATEADD(m, -1, @Period) SELECT DATEADD(s, -1, @Period) " failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The Online Books are not helpful. They just say you have to bind the parameters to the application variables.
What am I doing wrong?
I want to set another variable called "PeriodEndDate" also. Can both variables be set in the same task?
ASP2.0 .NET MS Web Developer express, MS SQL express.
with SqlDataSource control, i established a connection to database, now without Data controls like gridview, etc, can i get data from the database using commands (sql statements) ? how?
say i have SqlDataSource, i want to get the first row first column data to pass it through (response.redirect()), is that possible and how?
i really appriciate you help
Hi All, I have to return a string value from the store procedure. If condition success BEGIN RETURN 'Success' END ELSE BEGIN RETURN 'Fail' ENDI am retrieving this value with ExecuteReturnQuery() method. But it gives me the error like "Conversion failed when converting the varchar value to data type int."can anyone please help me for this?Thank you.Regards.
View 5 Replies View RelatedThis 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...
Up till now I've used SP's for updates and only ever needed to return error messages.
Now I have an SP that checks and validates something and has to return a string containing the result, (always a string/varchar!)
It works fine in Query Analyzer, I just need a demo of how to incorporate it into a VB app.
Hope that makes sense.
Thanks
Mark
Cannot see where I am going wrong. I always get a value of 0. I know my function works correctly, so it must be the VB.
CREATE FUNCTION [dbo].[getNextProjectID] ()
RETURNS varchar(10) AS
BEGIN
'''''''''''''''''''...........................
DECLARE @vNextProjectID varchar(10)
RETURN @vNextProjectID
END
Sub LoadNextProjectNumber()
Dim vProjectID As String
Dim cmd As New SqlClient.SqlCommand()
cmd.Connection = sqlConn
cmd.CommandText = "getNextProjectID"
cmd.Parameters.Add("@vNextProjectID", vProjectID)
cmd.Parameters("@vNextProjectID").Direction = ParameterDirection.ReturnValue
cmd.ExecuteScalar()
vProjectID = cmd.Parameters("@vNextProjectID").Value
txtProjectID.Text = vProjectID
cmd.Dispose()
End Sub
I have data coming back like below
140 KB 8 KB 1450 KB
I would like to manipulate the string to pull out only the number value. There is always a space between the number and the "KB". Looked at replace but got stuck, any help appreciated.