I have searched this forum for how to call a store procedure with
parameter and so far no result has answered my question yet. I have the
following store procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE sqlSearch
-- Add the parameters for the stored procedure here
@strSearch varchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR
[title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" +
@strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR
[content_nws] LIKE '%" + @strSearch + "%')
END
GO
I got this far in my code on how to call the parameter store procedure.
string strConn = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(strConn);
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlConn;
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.CommandText = "sqlSearch";
sqlComm.Parameters.Add("@strSearch", SqlDbType.VarChar); I don't know what do next.
I have a stored procedure that creates a normalized table from an existing denorm table. So I just need a simple way to call this SP from an aspx page. It would be good though to know how many records were effected, but this is not a requirement.
How can I get MS ACCES database records from an sql Stored Procedure. Idea is I have a SQL stored procedure, which wants to calculate a few things using some data from an MS access DB. Is it possible or am I talking against any DB principles?
Hello, I have one store procedure that writes something data to one physical sql table 'MyTable' and on beginning I first call:DELETE FROM MyTableProcedure returns at the end data from 'MyTable' as on SELECT. PROBLEM:I started SQL Manager on my Laptop and on Computer near me and all pointed to same database (on Server) and I run that procedure simultaneously on Laptop and Computer at the same time (two hands on F5 button on these computers). Occasionally when clicking with both hands at the same time I got no records at the end of execution (on both computers), otherwise I got the results.WHAT I THINK IS PROBLEM:Somhow one SP start working and it did not come to the end and another computer called this SP and execute first command (DELETE FROM MyTable) and I got empty records at the end..WHAT I NEED:I need to prevent this situation to get empty records. All computers must get these records always. How I can use some sort of LOCKs to do this?Or to do that on C# application level using LOCK command? Thanks in advanceAleksandar
I'm trying to create a Grid View from a stored procedure call, but whenever I try and test the query within web developer it says I did not supply the input parameter even though I am.I've run the stored procedure from a regular ASP page and it works fine when I pass a parameter. I am new to dotNET and visual web developer, anybody else had any luck with this or know what I'm doing wrong? My connection is fine because when I run a query without the parameter it works fine.Here is the code that is generated from web developer 2008 if that helps any...... <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMS3TestConnectionString %>" ProviderName="<%$ ConnectionStrings:IMS3TestConnectionString.ProviderName %>" SelectCommand="usp_getListColumns" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:querystringparameter DefaultValue="0" Name="FormID" QueryStringField="FormID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
I have this select statement: SELECT * FROM [enews] WHERE ([name_nws] LIKE '%" + strSearch + "%' OR [title_nws] LIKE '%" + strSearch + "%'OR [sub_nws] LIKE '%" + strSearch + "%' OR [sum_nws] LIKE '%" + strSearch + "%' OR [content_nws] LIKE '%" + strSearch + "%')
This statement works fine but when I turned it into a store procedure, it returns nothing. I created the store procedure as follows: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE sqlSearch -- Add the parameters for the stored procedure here @strSearch varchar(250) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR [title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" + @strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR [content_nws] LIKE '%" + @strSearch + "%') END GO
Hi, I am using a ListBox where a user can choose multiple lines. The index of the selected items are then used in a stored procedure.
I wan´t to use the ID´s in this statement: SELECT * FROM MyTable WHERE MyID IN (1,2,4,9)
But how can I do this? If I pass them as a string, then I can´t use them as above. Can I separate the string '1,2,4,9' so I can use them in the statement above? Or can I send the values as a array to the stored procedure?
Hi Am trying execute a store procedure with a date parameter than simply get back ever record after this todays date. It wont except the value i give. I can just do it in the store procedure as it will passed other values later on. It works fine if I take the parameter out, of both store procedure and code. It must be a syntax thing but im stuck sorry --- the error i get is: Incorrect syntax near 'GetAppointmentSessions'. here is my code: ' build calendar for appointment sessions Dim Today As Date = Date.Now Dim ConnStr As String = WebConfigurationManager.ConnectionStrings("ConnString").ConnectionString Dim Conn As New SqlConnection(ConnStr) Conn.Open()
Dim cmd As New SqlCommand("GetAppointmentSessions", Conn) cmd.Parameters.Add("InputDate", SqlDbType.DateTime).Value = CType(Today, DateTime) Dim adapter As New SqlDataAdapter(cmd)
Dim dt As New DataTable
adapter.Fill(dt)
Dim row As DataRow Here is the SQL:ALTER procedure [dbo].[GetAppointmentSessions]
@InputDate Datetime AS
SELECT TOP (5) uidAppointmentSession, dtmDate, (SELECT strRoomName FROM tblRooms WHERE (uidRoom = tblAppointmentSessions.fkRoom)) AS Room, (SELECT strName FROM tblHMResources WHERE (uidHMResources = tblAppointmentSessions.fkHMResource)) AS Clinician, dtmStartBusinessHours, dtmEndBusinessHours FROM tblAppointmentSessions
i am using asp.net 2005 with sql server 2005. in my database table contains Table Name : Page_Content
Page_Id 101 102
1 Abc Pqr
2 Lmn oiuALTER PROCEDURE [dbo].[SELECT_CONTENT] (@lang_code varchar(max)) AS begin declare @a as varchar(max)set @a = @lang_code
Select page_id,@a From page_content end Here in this above store procedure i want to pass 101 to @lang_code here is my output, but this is wrong output
Hi all,Could someone give me an example on how to create and execute the store procedure with using output parameter?In normal, I create the store procedure without using output parameter, and I did it as follow:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, etc...)ASBEGIN //my codeENDGOWhen i executed it, I would said: Execute NewEmployee 123456789, 'peter mailler', getDate(), etc....For output parameter:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, @message Varchar(40) out)ASBEGIN insert into Employee ...... //if error encountered set @message = "Insertion failure"ENDGOExec NewEmployee 123456789, 'peter mailler', getDate(), do I need to input something for the output parameter here?Anyone could give me an example on how to handle the output parameter within the store procedure coz I am not sure how to handle it?Many thanks.
Can I pass a sort expression into a store procedure as a parameter? Iwant to do sth like the following but do not know if it's possible.Maybe I need some tricks to work around?CREATE Procedure SelectAllRoles(@SortExpression varchar(20))ASIF @SortExpression <> ''THENSELECT Id, Name, Name2, LastUpdatedFROM [Role]ORDER BY @SortExpressionELSESELECT Id, Name, Name2, LastUpdatedFROM [Role]GOThanks!Zhu Ming
Some one please tell me how do I write store procedure that receives/takes parameter values I want to write store procedure which takes ID as a parameter
some one tell me how do I write store procedure that takes parameter if possibel please show me example of it
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
Is it possible to force a build to fail when a stored procedure in a project calls another stored procedure with one or more required parameters missing. E.g.:
Display based on customerid display max of item they purchased on a order display only number like cust id pursed 12 items in 3rd order so when i enter customerid it should display 12.
using row number in sql server 2012.creating storeprocedure accepting customer id as input parameter.
cid oid items 1 1 10 1 2 12 1 3 3 1 4 4
so if we enter 1 as custid it got to give us 12 as the result..
Hi, I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it. 1) I creat a class in Visual Basic.Net 2005, whose code is given below: Imports System Imports System.IO Imports Microsoft.VisualBasic Imports System.Diagnostics Public Class WLog Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String) Dim w As StreamWriter = File.AppendText(LogName) LogIt(newMessage, w) w.Close() End Sub Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter) wr.Write(ControlChars.CrLf & "Log Entry:") wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString()) wr.WriteLine(" :") wr.WriteLine(" :{0}", logMessage) wr.WriteLine("---------------------------") wr.Flush() End Sub Public Shared Sub LotToEventLog(ByVal errorMessage As String) Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog log.Source = "My Application" log.WriteEntry(errorMessage) End Sub End Class
2) Make & register its assembly, in SQL Server 2005. 3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile ( @LogName nvarchar(255), @NewMessage nvarchar(255) ) AS EXTERNAL NAME [asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile': System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied. System.UnauthorizedAccessException: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options) at System.IO.StreamWriter.CreateFile(String path, Boolean append) at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize) at System.IO.StreamWriter..ctor(String path, Boolean append) at System.IO.File.AppendText(String path) at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
I am working on a report where some customization is need to be delivered.. situation is , i have some parameter in report @USER_ID , @Report_Type where i am proving selection to user to select Report Type (Pending Or Completed) and passing USER_ID auto matically from URL string of user login(C# code).
I have another parameter @USER_IDS which is multiple selection for user and it will be filled with the users which lie under passed @USER_ID means i just need to add dataset with the query to select users from mapper table where reporting_head =@USER_ID, simple, but i have requirement to populate the underlined users with the selection of @USER_ID and @Report_Type and it need some TSQL code to populate so i am using Another store procedure and using same parameter as my main store procedure has .
Now i am using dataset with this store procedure to fill my @USER_IDS parameter
Both parameters value will be passed from main report parameters now , when i am previewing a report i am getting error
and i also tried to write exec statement in dataset query with the main repport paramters but exec is not supported ..
Hi I have below sql procedure CREATE PROCEDURE Qty @make as nvarchar(32)
AS select department as category from tblproducts where department = @make GO
Parameter (@make) will get the value from asp.net page
My Question: Is any possibilities to change the value of parameter when the procedure trigger. Examlpe like below
If @make = 'Sealed Lead Acid' then I need to pass parameter to the database as 'SLA' elseIf @make = 'Photographic' then 'Multimedia' elseif @make = 'cctv and accessories' then 'security'
and so on. I only have six categories. Please help
Hi all, I want to use a function with a tabel object as parameter. Doessomeone know a method to do this. I have read that a table as parameteris invalid.
We have a requirement to call a Web Service method from SSIS where the method accepts a single parameter that is derived from a database recordset and can exceed 4k characters. From the little we know about the Web Service task it is limited to only accepting direct input and variables. Does anyone have any suggestions to overcoming this issue? Any detailed information in possibly calling the Web Service from script or using an object variable would be extremely appreciated.
I need to be able to call a SSIS package with a specific parameter for one of the variables. Basically the package needs to be triggered from an application (.NET, C#) with a specific value.
The package is stored in the SQL Server (in Stored packages).
I have seen the article on dtexec and the SET flag, however we do not want to turn on xp_cmdshell on the SQL server due to the security implications, and that seems to be the only way to call dtexec
I have seen another article that says you can have a job invoke a package then have a specific user granted permission to the job, but that doesn't address how to set the parameter/variable for the package.
There are actually some other steps that technically need to run using the same parameter, so ideally if I could have a stored procedure call the SSIS package, then the subsequent toher stored procedures using the sent in parameter that would be great. I just can't figure out how to do this securely (as xp_cmdshell is not considered secure).
In t-sql 2012, the followinng sql works fine when I declare @reportID.
IF @reportID <> 0 BEGIN SELECT 'Students report 1' AS selectRptName, 1 AS rptNumValue UNION SELECT 'Students report 2', 2 UNION
[code]...
However when I use the sql above in an ssrs 2012 report, the query does not work since the @reportID parameter can have 0, 1, or up to 200 values.Thus I am thinking of calling the following following function to split out the parameter values:
FUNCTION [dbo].[fn_splitString] ( @listString VARCHAR(MAX) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT SUBSTRING(l.listString, sn.Num + 1, CHARINDEX(',', l.listString, sn.Num + 1) - sn.Num - 1) _id FROM (SELECT ',' + LTRIM(RTRIM(@listString)) + ',' AS listString) l CROSS JOIN dbo.sequenceNumbers sn WHERE sn.Num < LEN(l.listString) AND SUBSTRING(l.listString, sn.Num, 1) = ',' )
GO
how to remove the @reportID <> 0 t-sql above and replace by calling the fn_splitString function?
I would like to call the report from an external source: The report requires a paramater.
I tried the following: <a href='http://Report.aspx?ItemTakeOn%2fItemTakeOn&ProcessNo=278'> Report</a>"
The link works & i added the parameter in the link (ProcessNo=278) it opens the report but i still have to manually insert the Parameter. Also tried setting the parameter as hidden.