Solved

sql server update procedure

Posted on 2016-07-27
6
32 Views
Last Modified: 2016-07-27
I dont know the syntax, I have this so far I think you can see what I want to do.

USE [Dictionary]
GO

CREATE PROCEDURE [dbo].[usp_UpdateShortStrings]
(
@ClientCodeIn nvarchar(50) = NULL,
@StrShortUnTagged nvarchar(Max) = NULL,
@StrShortTagged nvarchar(Max) = NULL
)
AS
BEGIN
UPDATE dbo.TblWords
SET StrShort = @StrShortUnTagged,
SET StrShortTagged = @StrShortTagged 
WHERE ClientCode = @ClientCodeIn
END

Open in new window

0
Comment
Question by:PeterBaileyUk
  • 4
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>I have this so far I think you can see what I want to do.
The syntax looks correct, and I hate to guess what users' requirements are, so why don't you tell us what you're trying to pull off here, and any error messages that are displayed when you execute the above code.
0
 

Author Comment

by:PeterBaileyUk
Comment Utility
as it was just to understand the syntax for an update procedure I didnt expand, I will pass the client code, and the other two variables in vb.net app and it update the word table.

The vb code is here but I havnt made the call to the procedure yet. so a bit of a mute point.

the code creates the two strings i want to send to the table words

   Public Sub CreateShort()
        Dim dtCodes = New DataTable
        Dim dtCodeWords = New DataTable
        Dim dtCodeWordsT = New DataTable
        Dim cmd As New SqlCommand
        Dim CmdWords As New SqlCommand
        Dim StrClientCode As String
        Dim StrProcName As String
        Dim StrShortString As String
        Dim StrShortTaggedString As String

        StrProcName = "usp_getUnTaggedCodeWords"
        Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")

            cnSql.Open()
            cmd.Connection = cnSql
            cmd.CommandText = "SELECT TblWords.ClientCode FROM TblWords LEFT JOIN TblWordTags ON TblWords.ClientCodeWordPosition = TblWordTags.ClientCodeWordPosition WHERE (((TblWordTags.ClientCodeWordPosition) Is Not Null)) GROUP BY clientcode;"

            Dim reader As SqlDataReader = cmd.ExecuteReader()
            dtCodes.Load(reader)

            For Each drRecord As DataRow In dtCodes.Rows

                StrClientCode = drRecord("ClientCode")
                If StrClientCode = "069558C" Then
                    MessageBox.Show("now")
                End If

                dtCodeWords = GetShortStringWordsData(StrClientCode, False)

                For Each drRecordW As DataRow In dtCodeWords.Rows
                    StrShortString = StrShortString & drRecordW("word") & Chr(32)
                    StrShortString = RTrim(StrShortString)
                    Debug.Print(StrShortString)
                Next

                dtCodeWordsT = GetShortStringWordsData(StrClientCode, True)

                For Each drRecordWT As DataRow In dtCodeWordsT.Rows
                    StrShortTaggedString = StrShortTaggedString & drRecordWT("word") & Chr(32)
                    StrShortTaggedString = RTrim(StrShortTaggedString)
                    Debug.Print(StrShortTaggedString)
                Next







            Next


        End Using
    End Sub

Open in new window

0
 

Author Comment

by:PeterBaileyUk
Comment Utility
ive attached the screenshot form sql server it has red underlines so the syntax must be wrong
ee.JPG
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
Comment Utility
Second thought, I see the error, the second SET statement should not be there.  In a single UPDATE with multiple columns the columns are separated only by commas.

USE [Dictionary]
GO

CREATE PROCEDURE [dbo].[usp_UpdateShortStrings]
(
@ClientCodeIn nvarchar(50) = NULL,
@StrShortUnTagged nvarchar(Max) = NULL,
@StrShortTagged nvarchar(Max) = NULL
)
AS
BEGIN
UPDATE dbo.TblWords
SET StrShort = @StrShortUnTagged,  StrShortTagged = @StrShortTagged  -- No SET before the second column
WHERE ClientCode = @ClientCodeIn
END
GO

Open in new window

0
 

Author Comment

by:PeterBaileyUk
Comment Utility
ok I worked it out myself;
USE [Dictionary]
GO

CREATE PROCEDURE [dbo].[usp_UpdateShortStrings]

@ClientCodeIn nvarchar(50) = NULL,
@StrShortUnTagged nvarchar(Max) = NULL,
@StrShortTagged nvarchar(Max) = NULL

AS
BEGIN
UPDATE dbo.TblWords
SET StrShort = @StrShortUnTagged, StrShortTag = @StrShortTagged 
WHERE ClientCode = @ClientCodeIn
END

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
Comment Utility
Thank you I experimented before i saw your answer, its fine now thx
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now