How do I pass system variables to a stored procedure? Is it possible to have an OLE DB transformation with the following sql command: exec InsertIntoLog @MachineName, @TaskName...? Do I have to use a Derived Transformation first to 'convert' variables into columns and then use exec InsertIntoLog ?, ? ...
Thanks for the help.
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?
Hello all,
I have written an XP for SQL Server 2000 SP2. It performs as expected if I call the XP with literal values for the parameters, however when I wrap the XP call into a regular stored procedure, only the first character of each input string is seen by the XP! Here are the relevant code snippets:
C++ Extended Stored Procedure:
(Basically all this code is doing is retrieving the parameters and printing them back out)
srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, NULL, &bNull);
param1 = new BYTE[uLen + 1];
srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, param1, &bNull);
param1[uLen] = ' ';
srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, NULL, &bNull);
param2 = new BYTE[uLen + 1];
srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, param2, &bNull);
param2[uLen] = ' ';
srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, NULL, &bNull);
param3 = new BYTE[uLen + 1];
srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, param3, &bNull);
param3[uLen] = ' ';
sprintf(msgText, "Params received by xp: %s, %s, %s", param1, param2, param3);
srv_sendmsg( srvproc, SRV_MSG_ERROR, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msgText,SRV_NULLTERM);
srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)0);
Calling the XP with literal values:
EXEC xp_mytest 'one','two','three'
Output:
Params received by xp: one, two, three
Calling XP via a stored procedure:
create procedure sp_mytest
(
@myvar1 nvarchar(200),
@myvar2 nvarchar(50),
@myvar3 nvarchar(50)
)
as BEGIN
PRINT @myvar1
PRINT @myvar2
PRINT @myvar3
EXEC xp_mytest @myvar1, @myvar2, @myvar3
END
EXEC sp_mytest 'one','two','three'
Output:
one
two
three
Params received by xp: o,t,t
Any insight or assistance is greatly appreciated!!!
In this line, @BaseName varchar(50) is polulated by a cursor that queries a table for names of other databases. In this first example it works as predicted:
EXEC('SELECT COUNT (IdPartition) FROM '+@BaseName+'..SAVESET SS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentity WHERE [IdPartition] = 0 AND StoreIdentifier IS NULL')
If I create this as an SP (I want the output into another table)
CREATE PROCEDURE GetPArtitionItems @BaseName varchar(50),@IdPartition int, @PartitionItems int OUTPUT
AS
SELECT COUNT (IdPartition) FROM ['+@BaseName+']..SAVESETSS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentityWHERE [IdPartition] = @IdPartition AND StoreIdentifier IS NULL
GODeclare @PartitionItems intEXECUTE GetPartitionItems 'evmailboxstore1',0,@PartitionItems OUTPUT --EvMailboxStore1 is another table in the same database.
I get: Server: Msg 208, Level 16, State 1, Procedure GetPArtitionItems, Line 7Invalid object name ''+@BaseName+'..SAVESET'
In this case the value is not passed into the @baseName-variable. What do I do wrong?
Thanks in advance - Tim Kuhnell
I am having a challenge to pass the filename variable from SSIS Foreach Loop Container to SQL store procedure. I have an "Execute SQL Task" inside "Foreach Loop Container" which will receive the input filename parameter passed by the Foreach Loop Container. The store procedure command line is defined as "EXEC sp_mySQLStoreProcedure ?" within the "Execute SQL Task". The Foreach loop container will gather the filenames from File System then put it in the filename variable one by one. I would like to pass the filename variable as the input parameter to the sp_mySQLStoreProcedure in the "Execute SQL Task". How can I connect the variable and the store procedure so that it will process all the input files gathered by the Foreach Loop Container?
View 4 Replies View RelatedI have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
Hi!
I am kind of new to the sequel server and I have to write a stored procedure to create raw of my table. I wrote the following stored procedures, but I am not sure weather I can use variables in select statements as in the following procedure. Could you please explain what is the problem, why I am getting an error, when I am trying to create it? <The error says that there is an incorrect syntax near '@tbleName' in lines 21, 25 and 30>
Thank you very much!
Elly.
/* Inserts raw into the talble tbleName. Called from other procs.
tbleName: The name of the table to insert
other variables: values to insert
checkParentId,..: items to be checked upon-parentid, position, parent primary key
Returns:
0 : Successfully inserted a raw into the table
-1 : Position to insert is taken
-16: The specified parent does not exist
-17: Insert was unsuccessful
*/
create procedure sp_acml_createRaw (@tbleName VARCHAR(50), @nameN VARCHAR(50),
@hrefN VARCHAR(50), @posN VARCHAR(50), @parentIdN VARCHAR(50),
@nameV VARCHAR(150),@hrefV VARCHAR(255),
@posV SMALLINT, @parentIdV SMALLINT,
@checkParentId VARCHAR(50), @checkPosition VARCHAR(50),
@checkParentKey VARCHAR(50))
as
BEGIN
/* Check weather the parent exist */
IF exists (select @parentIdN
from @tbleName
where @tbleName.@checkParentKey = @parentIdV) /* if parent exist */
BEGIN
/* Check weather the isertion position is correct */
IF exists (select @checkParentId from @tbleName
where @tbleName.@checkParentId = @parentIdV AND
@tbleName.@checkPosition = @posV)
return -15 /*Insertion position is incorrect - it is taken already*/
/* Insertion postion is correct */
insert into @tbleName(@tbleName.@nameN, @tbleName.@hrefN, @tbleName.@posN, @tbleName.@parentIdN)
values (@nameV, @hrefV, @posV, @parentIdV)
IF (@@ERROR != 0)
return -17 /* Insert was unsuccessful */
return 0
END
return -16 /* The parent specified was not found */
END
/* End of sp_acml_createRaw */
I am writing a stored procedure that basically gets a whole lot of info. Simple enough... that part works. Now I want to add functionality to pass in sortBy and direction variables so the results can be sorted from a web page. The code I have is:
Code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure GetRecruiterApplicants
@useridvarchar(50),
@trackerGroup varchar(50),
@sortBy varchar(20),
@dir varchar(5)
as
begin
set nocount on
SELECT C.USERID INTO #VU FROM VUSERS V WITH (NOLOCK), COMPANIES C WITH (NOLOCK)
WHERE CHARINDEX(','+C.USERID+',', ','+VUSERS+',')>0 AND V.USERID=@userid
UNION SELECT @userid
SELECT distinct l.AccessCode, l.ApplicantGivenName, l.ApplicantFamilyName, l.DateCreated, l.DateApplicantAccessed, p.ApplicationTitle, l.disabled
FROM chamslinks l, chamsProjectIDs p, chamsGroups g
WHERE l.TrackerID IN (SELECT userid FROM #VU)
AND g.trackerGroup = @trackerGroup
AND p.groupTblID = g.groupTblID
AND p.ProjectID = l.ProjectID
ORDER BY l.@sortBy @dir
DROP Table #VU
set nocount off
end
The error I am getting is: "Server: Msg 170, Level 15, State 1, Procedure GetRecruiterApplicants, Line 24
Line 24: Incorrect syntax near '@dir'."
Any help?
i ahve the following procedure
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create PROCEDURE testflu @p_acad_period nvarchar (5)
AS
SET NOCOUNT ON
/* Base Table fields */
DECLARE @t_student_id nvarchar (11)
DECLARE @full_time_student bit
DECLARE @student_id nvarchar (11)
DECLARE @acad_period nvarchar (5)
DECLARE @aos_code nvarchar (11)
DECLARE @aos_period nvarchar (5)
DECLARE @full_desc_static nvarchar (200)
DECLARE @full_desc_session nvarchar (200)
DECLARE @dept_code nvarchar (10)
DECLARE @aos_start_dt datetime
DECLARE @exp_length smallint
DECLARE @unit_length nvarchar (10)
DECLARE @student_year tinyint
DECLARE @hrs_per_week int
DECLARE @aos_end_dt datetime
DECLARE @no_of_weeks numeric
DECLARE @hrs_total decimal (18,2)
DECLARE @main_course_study_is_C2K tinyint
DECLARE @hrs_actual decimal (18,2)
DECLARE @hrs_actual_student decimal (18,2)
DECLARE @hrs_notional_course decimal (18,2)
DECLARE @hrs_notional_student decimal (18,2)
DECLARE @student_type nvarchar (10)
DECLARE @moa_code nvarchar (10)
DECLARE @qual_aim nvarchar (3)
DECLARE @numeric_qual_aim int
DECLARE @subject_code nvarchar (4)
DECLARE @subject_area_main nvarchar (10)
DECLARE @subject_area_group nvarchar (10)
DECLARE @course_group char (1)
DECLARE @FE_HE_type char (2)
DECLARE @nvq_lvl_ind nvarchar (1)
DECLARE @geolocn_code nvarchar (10)
DECLARE @surname nvarchar (40)
DECLARE @forename nvarchar (40)
DECLARE @birth_dt datetime
DECLARE @post_code nvarchar (8)
DECLARE @student_attend_mode nvarchar (10)
DECLARE @funding decimal (18,2)
DECLARE @key_skill_count smallint
DECLARE @additionality_yn bit
DECLARE @fee_waiver nvarchar (10)
DECLARE @start_date datetime
DECLARE @student_status nvarchar (10)
DECLARE @end_date datetime
DECLARE @spec_learn_disab nvarchar (2)
DECLARE @sldd_ind tinyint
DECLARE @age smallint
DECLARE @full_time_ind tinyint
DECLARE @age_1960_ind tinyint
DECLARE @tsn_ind tinyint
DECLARE @link_ind tinyint
DECLARE @ESOL nvarchar (3)
DECLARE @esol_ind tinyint
DECLARE @infill_ind tinyint
DECLARE @RP_entitlement tinyint
DECLARE @threshold1 datetime
DECLARE @threshold2 datetime
DECLARE @threshold3 datetime
DECLARE @student_full_cost_ind tinyint
DECLARE @fundable_ind tinyint
DECLARE @LP_entitlement tinyint
DECLARE @LP2_entitlement tinyint
DECLARE @LP3_entitlement tinyint
DECLARE @outcome nchar (10)
DECLARE @OP_entitlement tinyint
DECLARE @tenhour_ind tinyint
DECLARE @basicIT nchar (3)
DECLARE @basic_it_ind tinyint
DECLARE @special_needs_ind tinyint
DECLARE @Evening_course tinyint
DECLARE @RP_basic decimal (18,2)
DECLARE @RP_NTETS_weight decimal (18,2)
DECLARE @RP_enhance_NTETS decimal (18,2)
DECLARE @RP_enhance_Age decimal (18,2)
DECLARE @RP_enhance_TSN decimal (18,2)
DECLARE @RP_enhance_IT decimal (18,2)
DECLARE @RP_enhance_SLDD decimal (18,2)
DECLARE @RP_enhance_ESOL decimal (18,2)
DECLARE @RP_total decimal (18,2)
DECLARE @LP_basic decimal (18,2)
DECLARE @subj_area_weight decimal (18,2)
DECLARE @LP_weighted decimal (18,2)
DECLARE @LP_enhance_SLDD decimal (18,2)
DECLARE @LP_enhance_VOC decimal (18,2)
DECLARE @LP_enhance_AGE decimal (18,2)
DECLARE @LP_enhance_TSN decimal (18,2)
DECLARE @LP_enhance_HE decimal (18,2)
DECLARE @LP_enhance_FEE decimal (18,2)
DECLARE @LP_total decimal (18,2)
DECLARE @OP decimal (18,2)
DECLARE @spurs_total decimal (18,2)
DECLARE @student_SPURS_total decimal(18,2)
DECLARE @out_weight decimal (18,2)
DECLARE @main_course_study tinyint
DECLARE @exp_days int
DECLARE @act_days int
DECLARE @total_students bigint
DECLARE @total_enrolments bigint
DECLARE @acad_year_spurs decimal (18,2)
DECLARE @RP_1960 tinyint
DECLARE @RP_null_DOB tinyint
DECLARE @RP_TSN tinyint
DECLARE @RP_disab2 tinyint
DECLARE @RP_disab3 tinyint
DECLARE @RP_disab4 tinyint
DECLARE @RP_ESOL tinyint
DECLARE @LP_1960 tinyint
DECLARE @LP_null_DOB tinyint
DECLARE @LP_TSN tinyint
DECLARE @LP_disab2 tinyint
DECLARE @LP_disab3 tinyint
DECLARE @LP_disab4 tinyint
DECLARE @LP_infill tinyint
DECLARE @LP_infill_1960 tinyint
DECLARE @LP_infill_null_DOB tinyint
DECLARE @LP_infill_TSN tinyint
DECLARE @LP_infill_disab2 tinyint
DECLARE @LP_infill_disab3 tinyint
DECLARE @LP_infill_disab4 tinyint
DECLARE @LP_term2 tinyint
DECLARE @LP_term2_1960 tinyint
DECLARE @LP_term2_TSN tinyint
DECLARE @LP_term2_disab2 tinyint
DECLARE @LP_term2_disab3 tinyint
DECLARE @LP_term2_disab4 tinyint
DECLARE @LP_term3 tinyint
DECLARE @LP_term3_1960 tinyint
DECLARE @LP_term3_TSN tinyint
DECLARE @LP_term3_disab2 tinyint
DECLARE @LP_term3_disab3 tinyint
DECLARE @ndaq_qual nvarchar (12)
DECLARE @base CURSOR
UPDATE delflu
SET tot_students = 0,
tot_enrolments = 0,
flu_total = 0,
processing = 1,
process_start = GETDATE(),
process_end = NULL
WHERE acad_period = @p_acad_period
DELETE FROM delflubase
WHERE (acad_period = @p_acad_period)
SET @total_students = 0
SET @total_enrolments = 0
SET @acad_year_spurs = 0.00
SET @base = CURSOR LOCAL FAST_FORWARD
FOR
SELECT stmaos.student_id, stmaos.acad_period, stmaos.aos_code, stmaos.aos_period, stcstatd.full_desc, stcsessd.full_desc AS SessionDescription,
stcsessd.dept_code, stcsessd.aos_start_dt, stcsessd.exp_length, stcsessd.unit_length, ISNULL(stmaos.student_year, 0) AS student_year,
stcsessd.hrs_per_week / 100 AS hrs_per_week, stcsessd.aos_end_dt, stcsessd.no_of_weeks, stcsessd.hrs_total / 100 AS hrs_total,
stmfesqa.student_type, stcsessd.moa_code, stcfesdt.qual_aim, stcfesdt.subject_code, delsubj.subj_area, ISNULL(delsubj.subj_course_group, 0)
AS subj_course_group, stcfesdt.nvq_lvl_ind, stcsessd.geolocn_code, stmbiogr.surname, stmbiogr.forename, stmbiogr.birth_dt, stmadres.post_code,
stmaos.attend_mode, stmfesqa.fee_override, stmaos.start_date, RTRIM(stmfesqa.course_status) AS course_status, stmfesqa.course_status_dt as end_date,
stmfesqa.spec_learn_disab, FLOOR(DATEDIFF(day, stmbiogr.birth_dt, stsacper.age_date))/365 AS Age, delESOL.subj_code AS ESOL, delspurs.threshold1, /* Log No.132694 - SQL changed*/
delspurs.threshold2, delspurs.threshold3, stmfesqa.outcome, delbasicIT.qual_aim AS basicIT, ISNULL(delsubjarea.subj_area_weight, 0.00)
AS subj_area_weight, ISNULL(deloutwgt.weighting, 0.10) AS weighting, ISNULL(stmaos.additionality_yn, 0) AS additionality_yn,
nicisreports.dbo.ndaqquals.qual_ref
FROM stsacper INNER JOIN
stmaos INNER JOIN
stcsessd ON stmaos.aos_code = stcsessd.aos_code AND stmaos.acad_period = stcsessd.acad_period AND
stmaos.aos_period = stcsessd.aos_period INNER JOIN
stcfesdt ON stmaos.acad_period = stcfesdt.acad_period AND stmaos.aos_code = stcfesdt.aos_code AND
stmaos.aos_period = stcfesdt.aos_period INNER JOIN
stmfesqa ON stmaos.student_id = stmfesqa.student_id AND stmaos.aos_code = stmfesqa.aos_code AND
stmaos.acad_period = stmfesqa.acad_period AND stmaos.aos_period = stmfesqa.aos_period INNER JOIN
stmbiogr ON stmaos.student_id = stmbiogr.student_id ON stsacper.acad_period = stmaos.acad_period AND
stsacper.start_date <= stcsessd.aos_start_dt AND stsacper.end_date >= stcsessd.aos_end_dt INNER JOIN
delspurs ON stmaos.acad_period = delspurs.acad_period INNER JOIN
stcstatd ON stmaos.aos_code = stcstatd.aos_code LEFT OUTER JOIN
deloutwgt ON stcfesdt.nvq_lvl_ind = deloutwgt.nvq_lvl_ind LEFT OUTER JOIN
delbasicIT ON stcfesdt.qual_aim = delbasicIT.qual_aim AND stcfesdt.subject_code = delbasicIT.subj_code LEFT OUTER JOIN
delESOL ON stcfesdt.subject_code = delESOL.subj_code LEFT OUTER JOIN
stmadres ON stmbiogr.student_id = stmadres.student_id AND stmbiogr.perm_add_id = stmadres.add_id LEFT OUTER JOIN
delsubjarea RIGHT OUTER JOIN
delsubj ON delsubjarea.subj_area = delsubj.subj_area ON stcfesdt.subject_code = delsubj.subj_code left outer join
stcstdet on stmaos.aos_code = stcstdet.aos_code inner join
nicisreports.dbo.ndaqquals on replace(stcstdet.text_field1,'/','') = nicisreports.dbo.ndaqquals.qual_ref
where stmaos.acad_period = @p_acad_period
and (stmaos.return_ind = 'F' OR stmaos.return_ind = 'B')
AND (stmaos.stage_ind = 'E')
AND (stcfesdt.qual_aim IS not null)
AND (stcfesdt.qual_aim IS not null)
and (stmfesqa.fund_source <> '09' OR stmfesqa.fund_source IS NULL)
and stmaos.start_date is not null
and stmaos.start_date <= getdate()
OPEN @base
FETCH NEXT FROM @base
INTO @student_id, @acad_period, @aos_code, @aos_period, @full_desc_static, @full_desc_session,
@dept_code, @aos_start_dt, @exp_length, @unit_length, @student_year,
@hrs_per_week, @aos_end_dt, @no_of_weeks, @hrs_total,@student_type,
@moa_code, @qual_aim, @subject_code,@subject_area_group, @course_group,
@nvq_lvl_ind, @geolocn_code, @surname, @forename, @birth_dt, @post_code,
@student_attend_mode, @fee_waiver, @start_date, @student_status, @end_date,
@spec_learn_disab, @age, @ESOL, @threshold1, @threshold2, @threshold3, @outcome, @basicIT,
@subj_area_weight, @out_weight, @additionality_yn,@ndaq_qual
when i run the follwoing i get
exec testflu '06/07'
Server: Msg 16924, Level 16, State 1, Procedure testflu, Line 210
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
i am unsure cos from what i can see i ahve included all, am i missing something
I have successfully Create a Site, Inserting Updating and
Deleting information in my DB all with Stored Procedures, But I need the
ability to pass their username into my Stored Procedures. How and where do I
code this in my ASPX file?
My SP would be something like this
Create procedure test
@UserName
varchar(50)
As
Select *
From table
Where username
= @username
All of the
data is tied to the user in one way or another but I do not know what code to
put in my page?
Anyone got an example of passing xml to a stored procedure and within that procedure, grabbing values out of the xml to perform an insert ?
View 1 Replies View RelatedI have two variables in my code behind .cs,
string Zip;int MenuCode;
I am trying to pass them to the stored procedure below and am getting compile errors. How do I properly construct the parameter object?
error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string"
<asp:SqlDataSource ID="LocalPremiumLinks" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter Name="Zip" Type="string" /> <asp:Parameter Name="MenuCode" Type="double" /> </SelectParameters>
Hi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors.
Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors.
The error i get is: Procedure or function 'SPOne' expects parameter '@publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails.
SPOne is as follows: ALTER PROCEDURE dbo.StoredProcedureOne @typeID smallint=null, @title nvarchar(MAX)=null, @publicationID smallint OUTPUTASBEGIN SET NOCOUNT ON INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) SELECT @publicationID = scope_identity()
END
and SPtwo is as follows: ASDECLARE @NewpublicationID IntEXEC StoredProcedureOne @NewpublicationID = OUTPUTSET @publicationID = @NewpublicationIDINSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID)
SELECT @NewpublicationID
Thanks
Gemma
HII m trying to use the following Store Procedure to search Suppliers... user provides a Column Name (Search By) and an Expression... but i m not getting it work properly... plz review it and tell me wots wrong with this stored procedure is... CREATE PROCEDURE dbo.SearchSupplier ( @Column nvarchar(50), @Expression nvarchar(50) ) AS SELECT SupplierID,Name,Address,Country,City,Phone,Fax FROM Supplier WHERE @Column LIKE '%'+@Expression +'%' OR @Column = @Expression RETURN Thanks
View 2 Replies View RelatedHi i have some radio buttons, what i want to achieve is that if one of the radio buttons is clicked then the value is sent to the stored procedure, as as now the radio buttonlist is like this; <asp:RadioButtonList ID="SortButtons" runat="Server" RepeatDirection="Horizontal" RepeatLayout="Flow" Width="69px" OnSelectedIndexChanged="SortButtons_SelectedIndexChanged"><asp:ListItem Text="Price" Value="productPrice"></asp:ListItem><asp:ListItem Text="Quantity " Value="Qty"></asp:ListItem></asp:RadioButtonList> My question is how will my stored procedure take in the radio button that has been clicked and order it by that value, e.g if price is clicked how will the stored procedure take taht in, this is how my normal unordered stored procedure currently looks; CREATE PROCEDURE stream_CategoryResults@subCategoryID INT ASSELECT DISTINCT Companies.companyName, Companies.companyLogo,SubCategories.subCategoryName, Products.productPrice, Products.productInfoURL FROM Companies INNER JOIN Products ON Companies.companyID = Products.companyID INNER JOIN SubCategories ON Products.subcategoryID = SubCategories.subCategoryID WHERE SubCategories.subCategoryID=@subCategoryID
View 8 Replies View RelatedHey huys, I got this stored procedure. First of all: could this work?
--start :)
CREATE PROCEDURE USP_MactchUser
@domainUserID NVARCHAR(50) ,
@EmployeeID NVARCHAR(50) ,
@loginType bit = '0'
AS
INSERT INTO T_Login
(employeeID, loginType, domainUserID)
Values
(@EmployeeID, @loginType, @domainUserID)
GO
--end :)
then I got this VB.Net code in my ASP.net page....
---begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed
End Sub
---
If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?
I want to pass and array of ids to a procedure for inserting i a relation table.
I found some examples in other posts, but had problems getting them to work.
I just want to pass a parameter with value like '1,45,89' to the procedure, then loop through it to insert the relations.
(I´m using sql server 2000), had some problem with examples with strpos then.
Any hints ?
peace.
hi,i have a stored procedure that is used to insert the employee data into a EMPLOYEE table.now i am passing the employee data from sqlCommand.i have the XML string like this'<Employee><Name>Gopal</Name><ID>10157839</ID><sal>12000</sal><Address>Khammam</Address></Employee>' when i pass this string as sql parameter it is giving an error. System.Data.SqlClient.SqlException: XML parsing error: A semi colon character was expectedbut when i execute the stored procedure in query analyzer by passing the same parameter. it is working.please reply me on gk_mpl@yahoo.co.in
View 1 Replies View RelatedHi, that's the problem:
I have a GridView, bound to a SQLDataSource, with an stored procedure as a Select query. The Select Parameters are bound to controls in the web form, acting like some filter fields.
When I submit the page, everythings works fine, except when I try to set some value in the DateTime fields. .Net is enclosing the date with extra single quotes, as I could see in the Profiler:
exec sel_despesa_procura @codigo=NULL,@fornecedor=NULL,@descricao=NULL,@vencto_ini=''2005-10-10 00:00:00:000'',@vencto_fim=''2005-10-20 00:00:00:000'',@pagto_ini=NULL,@pagto_fim=NULL,@valor=NULL,@valor_pago=NULL,@centro_custo=NULL,@pago=N'0,1'
The fields are defined as follows:
<SelectParameters>...<asp:ControlParameter ControlID="txtFiltroVencIni" Name="vencto_ini" PropertyName="Text" Type="DateTime" /><asp:ControlParameter ControlID="txtFiltroVencFim" Name="vencto_fim" PropertyName="Text" Type="DateTime" />...</SelectParameters>
The stored procedure doesn't even execute, due to the bad formatted arguments. It returns the error:
Msg 102, Level 15, State 1, Line 1Incorrect syntax near '2005'.
I'm going to change the parameter type to varchar, as a workaround, but I'd like to solve this problem.
Thanks in advance,
Anderson
I have the following stored procedure
ALTER PROCEDURE dbo.TextEntered
@searchString varchar(30)
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
Select GAVPrimaryKey, NAME from towns where NAME like @searchString order by NAME
RETURN
doesn't work!!!!
I would like to produce the following where s is @searchString:
Select GAVPrimaryKey, NAME from towns where NAME like 's%' order by NAME
Any ideas how I might acomplish this I have tried almost everything !
Hi All,I have sometimes used the following sort of query to pull data from one table to another:INSERT INTO Table1 SELECT fname, lname FROM Table2Now, let's suppose that I had created a stored procedure to do the insert (and any other logic i was concerned about) and I did something like this:EXECUTE Table1 _Insert SELECT fname, lname FROM Table2It won't work, giving an error that looks something like this:Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0Procedure 'Table1_Insert' expects parameter '@fname', which was not supplied.I assume I'm not doing things right... how would I pass a result set to a stored procedure, with each row corresponding to an input parameter of the stored procedure?
View 11 Replies View Relatedmake it so that I compare that parameter, that is a char(3), to something like "XXX" and then choose the correct SELECT statement to use depending on that parameter.... much like an if else.
Any help is GREATLY appreciated. Thanks.
Robert
Hello, I'd greatly appreciated if someone can help me out on this problem:
I'd like to pass a string (which is a multiple value in this case - "Smith", "Lee", "Jones", "Hanson") as an input parameter to a stored procedure. I'd like to use this string as part of the select statement:
exec sp_GetLN "smith, lee, jones, hanson"
In the stored procedure:
@strLN varchar <- "smith, lee, jones, hanson"
/*What do I need to do here to SET/REPLACE the original string so that the syntax can be accepted by the select statement??*/
SELECT * from tblCustomers
WHERE LASTNAME IN (@strLN)
It looks simple but I've been trying to get the syntax to work. What can I do to change the quote from ' to "?? Can I use char(34) like VB??
Your help is greatly appreciated~
Thank you
Is it possible to have a stored procedure that passes recordsets which are created and passed from different tables and joins the information in those recordsets to form one group of information?
View 1 Replies View RelatedHi all,
I want to use a stored procedure to access data in different databases. I was wondering if it is possible to pass the database name to the stored procedure as a parameter and use it in the sql statement.
Thank you for all the help you can give me.
Regards,
Eve
I am using VB6 and SQL Server 6.5. I want to pass a boolean to a SQL Server stored procedure. The code below is the main part that tries to pass 'True' to the stored procedure, but an error occurs when the line rst.Open.... is reached ? The error is as follows: The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.
I have tried putting 'True' directly in the rst.Open... line instead of assigning it to a boolean variable but the same error occurs.
The stored procedure works fine when run in SQL Server.
Can anyone help ? Please.
complete = True
Set rst = New ADODB.Recordset
rst.CursorType = adOpenForwardOnly
rst.LockType = adLockReadOnly
rst.Open "usp_CompleteUID_sel (" & complete & ")", UIDCon, , , adCmdStoredProc
Do While Not rst.EOF
lst_pastUIDs.AddItem (rst.Fields(1))
rst.MoveNext
Loop
rst.Close
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?
Hi, I'm fairly new to TSQL so I'll try to explain as best I can.
I'm using a stored procedure to return a recordset and I'm passing a variable/parameter into it from MS Access XP, the syntax for the SQL statement is something like the following (Extremely trimmed down) :
CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(8)
AS
declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode = @MyLsn)
The value I'm passing (@MyLSN) is 24/12359 (one LS Number)
The above statement works fine (ie returns a recordset). But what I want to be able to do is pass a series of LS numbers into the Stored Procedure. So I've opted for using the SQL 'IN' function. So the SQL statement now looks like:
CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(50)
AS
declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode IN (@MyLsn))
The value I'm passing (@MyLSN) is '24/12359','24/12360'
This is where the problem begins because the stored procedure now doesn't return a recordset.
If I run this SQL statement thru a VIEW (manually entering the LS numbers) then the correct recordset is returned.
Also if I run the Stored Procedure using the Query Analyzer (where it will prompt me for the value of @MyLsn) and enter just one LS Number then it works fine but if I try to supply 2 LS Numbers then nothing is returned. I've tried all manner of syntax when supplying the LS numbers but alas to no avail.
So if someone could point me in the right direction then that would be much appreiciated.
Thanks in advance.
I've created a stored procedure and I need to pass it a list of order IDs.The OrderSourceOrderID field is NVARCHAR(100) in the DB. <-Not sure if this piece of info makes a difference.
It all works, except for the @orderList variable. I'm listing OrderSourceOrderIDs that I do not want to appear, but they appear in the final list anyway. How would I pass this information the right way?
EXEC uspGetBuyCancelledOrders @orderList ='''12343'',''1122'',''123123132''', @companyID='123'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
Hey people,
I am trying to pass a parameter to a stored procedure based on a select statement. Basically it's something like this (in my head :P ):
foreach PersonID in (SELECT id FROM person)
{
MyStoredProcedure(PersonID);
}
Multiple rows will be returned from the select statement.
The code above will be in another stored procedure (not that code but an equivalent in SQL). Is there something like a PLSQL in SQL Server 2000? How can I translate that into a stored procedure for SQL?
Thanks to you all!
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
I am taking my first steps into stored procedures and I am working on a solution for efficiently paging large resultsets with SQL Server 2000 based on the example on 4Guys: http://www.4guysfromrolla.com/webtech/042606-1.shtml
The problem with my stored procedure is, is that it doesn't seem to recognize a variable (@First_Id) in my dynamic Sql. With this particular sproc I get the error message: "Must declare the scalar variable '@First_Id'"It seems to be a problem with 'scope', though I still can't yet figure out. Can anyone give me some hints on how to correctly implement the @First_Id in my stored procedure? Thanks in advance!
Here's the sproc:
ALTER PROCEDURE dbo.spSearchNieuws(@SearchQuery NVARCHAR(100) = NULL,@CategorieId INT = NULL,@StartRowIndex INT, @MaximumRows INT,@Debug BIT = 0)ASSET NOCOUNT ONDECLARE @Sql_sri NVARCHAR(4000),@Sql_mr NVARCHAR(4000),@Paramlist NVARCHAR(4000),@First_Id INT, @StartRow INTSET ROWCOUNT @StartRowIndexSELECT @Sql_sri = 'SELECT @First_Id = dbo.tblNieuws.NieuwsId FROM dbo.tblNieuwsWHERE 1 = 1'IF @SearchQuery IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)' IF @CategorieId IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND dbo.tblNieuws.CategorieId = @xCategorieId'SELECT @Sql_sri = @Sql_sri + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'SET ROWCOUNT @MaximumRows SELECT @Sql_mr = 'SELECT dbo.tblNieuws.NieuwsId, dbo.tblNieuws.NieuwsKop, dbo.tblNieuws.NieuwsLink, dbo.tblNieuws.NieuwsOmschrijving, dbo.tblNieuws.NieuwsDatum, dbo.tblNieuws.NieuwsTijd, dbo.tblNieuws.BronId, dbo.tblNieuws.CategorieId, dbo.tblBronnen.BronNaam, dbo.tblBronnen.BronLink, dbo.tblBronnen.BiBu, dbo.tblBronnen.Video, dbo.tblCategorieen.CategorieFROM dbo.tblNieuws INNER JOIN dbo.tblBronnen ON dbo.tblNieuws.BronId = dbo.tblBronnen.BronId INNER JOIN dbo.tblCategorieen ON dbo.tblNieuws.CategorieId = dbo.tblCategorieen.CategorieId AND dbo.tblBronnen.CategorieId = dbo.tblCategorieen.CategorieId WHERE dbo.tblNieuws.NieuwsId <= @First_Id AND 1 = 1' IF @SearchQuery IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)' IF @CategorieId IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND dbo.tblNieuws.CategorieId = @xCategorieId' SELECT @Sql_mr = @Sql_mr + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'IF @Debug = 1PRINT @Sql_mr SELECT @Paramlist = '@xSearchQuery NVARCHAR(100), @xCategorieId INT'EXEC sp_executesql @Sql_sri, @Paramlist, @SearchQuery, @CategorieIdEXEC sp_executesql @Sql_mr, @Paramlist, @SearchQuery, @CategorieId
Hi!
I need a stored procedure with this basic setup:
CREATE PROCEDURE test
@Type int
AS
SELECT *
FROM
Cards
WHERE
CASE @Type = 1111 THEN CardType = 1111 ELSE CardType = 2222 END
GO
I know that the part after WHERE is wrong. But what I would like to achieve is this:
if the @type variable equals 1111 then get alla the rows with that value in the CardType-column. The same if @type = 2222, and if @type is any other value, then choose all rows regardles of the CardType value.
How can this be done?
Thanks!
/Rickard