I’ve just begun to learn how to write stored procedures and SQL code outside of the basic DML stuff. Something that I’ve recently come across is table value parameters. I’ve found a script to make a TVP and it works just fine but there are two things that I don’t understand about it. One, when […]
I am creating a temporary table in a stored procedure like this: IF OBJECT_ID(‘#INSTL_PLAN_TEMP’) IS NULL BEGIN CREATE TABLE #INSTL_PLAN_TEMP(P_LOAN_ID INT, P_RECOVERY_TYPE VARCHAR(1), P_SEQ_NO INT, P_FROM_INSTL_NO INT) DROP TABLE #INSTL_PLAN_TEMP END After that I create a cursor on this table like DECLARE INSTL_PLAN_CUR CURSOR FOR SELECT P_RECOVERY_TYPE, P_LOAN_ID, P_SEQ_NO, P_FROM_INSTL_NO FROM INSTL_PLAN_TEMP WHERE P_LOAN_ID = […]
I tried to read the contents of single xml file in the local machine using bulk insert. SELECT * FROM OPENROWSET ( BULK ”’+ @FILENAME+”’ , SINGLE_CLOB ) AS xmlData It is working, but the same i am trying to read from the remote machine. its giving the following error Msg 4861, Level 16, State […]
How to fetch the return value from a stored procedure? I noticed that the stored procedure returns an integer on its own. I need to fetch it in C#.
I’m trying to create a stored procedure that will search all user created tables of a database for a search string. I found this stored procedure that I’d like to start off with and build on. http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm The only problem is entering a datetime into the search will always return nothing. How can I modify […]
Not so strong at Transact-SQL and stored procedures. I want to know where does that procedure save the data ? ALTER PROCEDURE [dbo].[InsurerCascoConditionsSave] @InsurerID int ,@Data xml AS BEGIN declare @DocHandle int EXEC sp_xml_preparedocument @DocHandle OUTPUT, @Data MERGE InsurerCascoConditions AS target USING (SELECT InsurerConditionID, @InsurerID, CascoConditionID, Value FROM OPENXML (@DocHandle, ‘/DocumentElement/Table1’, 2) WITH (InsurerConditionID int, […]
Can you show sample coding to create a trigger or stored procedure that maintains rows a SQL Server junction table when changes are made to the Authors and BookTitles tables such as inserting and updating rows in those tables? We have the following tables: Authors: ID NAME ZIP AND SOME MORE COLUMNS BookTitles: ID TITLE […]
Can we create a temporary table from the stored procedure results dynamically? I do not want to declare the temporary table columns manually. It shud take the schema of table from the stored procedure results.
The two servers have exactly the same setup (databases/tables etc). I want one of the tables (on both servers) to have the same data. So the update procedure on server A should also be able to update server B. I want this to happen with each call to that update stored procedure with out any […]
I would like to execute my MSSQL stored procedure from my java program by giving as parameters two dates to have aselection between this two dates. So in java code I proceeded like this: “EXEC [dbo].[MyProcedure] ” + “‘”+ dateFrom+”‘” ,”‘”+dateTo”‘” I have declared my parameters in sql server procedure like this way : @collected_date1 […]